What is a practical use for the function CELL()

[TheRealWhiteKnight]

Could anybody explain what is the practical use of the CELL() function when it is used in the attached examples from the user help facility?

Why would anybody need a function to repeat back to them what they just told that function?

It’s totally incompatible with Excel, wherein it returns the location of the last focused cell, providing valuable information for decision making. Quite specifically, for identifying a key to a lookup() that could feature in a known column on the unknown row where the cursor can be found.

Is it perhaps backward compatibility with Lotus 123?

Probably more importantly, is there a mechanism (and support) for requesting compatibility with Excel?

These are the function calls that seem to support the provision of an ashtray on a Harley Davidson:-

COL
Returns the number of the referenced column.
=CELL(“COL”;D2) returns 4. - I suppose it is possible that even if you don’t know that “D” is the 4th letter of the alphabet, it may be useful to have a permanent reminder cluttering up the sheet somewhere. An experiment identified that it can adapt to column insertions before D - wonderful, I now know that “E” is the 5th letter of the alphabet. Do users often need to know that column XEY2 is 16379?

ROW
Returns the number of the referenced row.
=CELL(“ROW”;D2) returns 2. - If the 2nd parameter isn’t provided, it just tells you “You typed into the row you’re on” - even more amusing "You’re really asking me for the row number of the row number you just gave me!"

SHEET
Returns the number of the referenced sheet.
=CELL(“Sheet”; Sheet3.D2) returns 3.

ADDRESS
Returns the absolute address of the referenced cell.
=CELL(“ADDRESS”;D2) returns $D$2.
=CELL(“ADDRESS”; Sheet3.D2) returns $Sheet3.$D$2.
=CELL(“ADDRESS”;‘X:\dr\test.ods’#$Sheet1.D2) returns ‘file:///X:/dr/test.ods’#$Sheet1.$D$2.

[Lupp]

Imo the basical concept, use cases, and result coding of CELL() are axtremely outdated. I suppose the function is still maintained only to not break old spreadsheets using it.
The only current use-case I can see is valid if “macro” usage isn’t possible/allowed:
=REGEX(CELL("filename");"^.*#\$"; ""; 1) will return the name of the current sheet. All the target versions must support the REGEX() function.

[TheRealWhiteKnight]

Hi Lupp,

Thanks for enlightening me.

I doubt anybody would entertain a request to make a redundant function provide the same redundancy as Excel

Dare I ask for more of your insight?

I had hoped to use it the way Excel works by very simply identifying the row on which the user was currently focused. That would allow me to target a known column with an INDIRECT() construction.

Without using a macro, which destroys the undo buffer, can you think of any other means of building the reference to say =VLOOKUP(“G”&…current row number)?

Many thanks I anticipation

[sokol92]

@TheRealWhiteKnight

Probably more importantly, is there a mechanism (and support) for requesting compatibility with Excel?

Yes, the CELL function is similar to the function of the same name in Excel.
In Excel, this function has actually existed for a long time. This function is volatile. Also, the function is locale dependent (see Note: in Microsoft documentation). In my opinion, using this function is appropriate in cases where other functions do not give the desired result.

[TheRealWhiteKnight]

Hi Sokol92,
You’ve certainly helped me see where the evaluation of formatting and content type and an alternative to the perennial IF(AANN="",…) avoidance of division by zero errors can be useful.
I’m still failing to understand how the return of “You are where you are” serves any purpose.
Welllllll, probably less of a failure to understand and more of "What a waste - that could easily have given me the information I needed to get the key to drive an enquiry on a lookup() AND provided dynamic updates if I entered data in another row of the same array.
Thanks for helping me learn a couple of new ideas.