@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
