Formula in a cell that is within a range referenced by the formula
@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:
Comments

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

SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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

Hahaha  "Is there content hiding here?"
Formula: "No...just ignore me..."
Help Article Resources
Categories
Check out the Formula Handbook template!