Formula in a cell that is within a range referenced by the formula

Paul Newcome

@Genevieve P & @Andrée Starå

I was finally able to do some testing based on THIS THREAD and the recent discovery that you can reference a range in a formula that covers the cell that the formula itself is in.

I got some interesting results when I used JOIN and JOIN/COLLECT. It treats the cell as blank, so if you just use JOIN between f and g below with a comma delimiter, you end up with

"a, b, c, d, e, f, , g, h, i, "

I also found that it HAS to be whole column references to work.

[Column1]:[Column1] --> Works

[Column1]:[Column15] --> Works

[Column1]1:[Column1]12 --> Does NOT work

[Column1]@row:[Column15]@row --> Does NOT work

And of course specifically referencing that cell does not work either.

HERE is the sheet I used for testing that shows what all I have tried thus far. I personally found the JOIN function tests to be pretty interesting.

Typing up this discussion got me to thinking, so I threw in another quick test that had more interesting results. The formula DOES LOOK AT ITSELF. It looks at itself as a blank. Typing up the JOIN test results hinted at it, so I did another COUNTIF test to count all cells in that column that were blank, and it included itself in that count.

Of course that prompted more testing such as MATCH where it was pulled as the count for the first blank cell in the range.

Overall I think it's pretty interesting how it is working...

EDIT:

Original "note" that spurned this testing:

"NOTE: If a formula is placed in the column it's referencing, it won't reference the cell that the formula is in. For example, if you were to place the example formula above in a column named Annual Budget, the SUM formula would sum all cells except for the cell containing the formula."

Link where note was found:

https://help.smartsheet.com/articles/2476816-create-cell-column-reference-formula?_ga=2.239377859.1550014678.1582219378-1924930788.1582219378

Genevieve P.

So... this is incredible. I'm favouriting this post so I can look at that testing sheet when needed!

Paul Newcome

Andrée Starå

@Paul Newcome

Paul Newcome

@Andrée Starå 😂😂

Paul Newcome

I added another one. I used an INDEX/MATCH where the INDEX function was looking at the column the formula is in. Set the MATCH up so that it would definitely only have the choice of the cell the formula is in, and it returned itself as blank.

I might be having a little too much fun with this whole "I am nothing" complex these formulas are having. Hahaha

Genevieve P.

Hahaha - "Is there content hiding here?"

Formula: "No...just ignore me..."