What is a practical use for the function CELL()

English
#1

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.

#4

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.

#5

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

#6

doesn’t make sense, imo. The first parameter must be an array (SheetCellRange) reference, and though a single cell also supports the SheetCellRange service any ?LOOKUP() based on a single cell seems absurd.

A whole column? Why?
Assuming you mean a range of known dimension starting at a specific cell of a specific column, OFFSET() is your friend. Tinkering range addresses as text is generally a bad idea, imo. Give me a clear counter example if you can.
An example with some explanations:
disask121893CalculatedReferences.ods (13.4 KB)

#8

You know the expected result while you enter the formula, and it may seem as if you can simply (?) use "D2" in the place.
However. if you change the structure of the sheet (insert/delete columns/rows/cell ranges) or move the cell with the formula by dragging, the relative address will adapt. Your string will not.

#9

Sorry, Bad explanation

Visualise an array say G4:P10
G5 contains a key to Table2
H5 is edited with a time
I5 is edited with a time
J5 is a calculated lapsed period
K5 =VLOOKUP(G5,Table2,5,1) EASY PEASY and pertinent variables are filled
L5 =VLOOKUP(G5,Table2,6,1) EASY PEASY and pertinent variables are filled
M5 =VLOOKUP(G5,Table2,6,1) EASY PEASY and pertinent variables are filled
Mini Dashboard above the array G4:P10
Needs to know which row is focused, then, whatever key in G&“ROW(x)” is identified, it can provide a window into Table2 and the user can see pertinent static information for the active key.
Ergo focus any cell in G4:P10 and the user gets the Dashboard pertinent to Gx
Easy Peasy in Excel but a nightmare in LO because macros eviscerate the undo buffer.
I’ve Never met a user who didn’t go “Oh My Good God - undo” or something similar
Confession, I’ve never mastered INDEX() so if there is a solution with that function then please hold my hand.
Thanks

#10

Sorry. I will no longer read (try to understand) this kind of posts.
Attach an example sheet with the formulas, and explain them in adjacent cells where needed.
Of course, you can also do without my support.

#12

RealCELL.ods (18.1 KB)
This is the use case. =“F”&CELL(“row”) achieves the objectively perfectly simply if CELL(“row”) did more than just tell you where you already know you are because you just typed a formula into the cell where you are🤦‍♂️

#14

Forget the "F&" for a while.
Now we get closer: It’s about the usage of functions with an optional parameter.
=CELL("row") and =ROW() both allow for an additional parameter giving the reference to a position for which you want to get the answer.
If you omit the additional argument, the current (formula-) cell is assumed in both cases.
=ROW() is surely simpler and does not contain a possibly locale-dependent part (string literal) which may not be automatically adapted “as need be”.
disask121893_ROW_wit_without_explicit_References.ods (10.1 KB)

#15

I can’t identify which one of the examples in your sheet advises me where I just put the cursor.
They all remain set in stone telling me 11, 13, 14, 22, 11, 29 & 30
If your formula is in F6 and I type “08:30” into Cell J16, I need something that isn’t a macro to tell me "You just used a cell in Row 16 - NOT there’s this formula in this cell and this cell is on row 6.
If I concatenate “F” and 16 I get F16 and F16 contains precisely the key I need for looking up the array containing the data I need.
I thought the example file RealCELL.ods showed that.
I cannot separate the functionality of identifying a row number and gluing an “F” to the front of it because that is the only place I can find my key to display the data to the user so the user can verify they didn’t charge the wrong project.
It’s not necessary to enter a key for every piece of data if the row is already on screen. In that way the user can move between projects and modify one piece of data in each and it immediately verifies the project in the “Dashboard” above the data.
The array used is only an example. The real sheet may easily contain 10,000 rows and if the user scrolls up to row 3 and focuses any cell on that row the project is displayed.
If the last 20 rows are on screen the user can pick any cell on any row and change any one value, again with instant verification of the project.
Add a new row, type a project ID that isn’t visible in those last 20 rows and the project data is displayed in the “Dashboard” and the user can add all or any value to the identified project.

#19

In Calc, it is easy to achieve the effect you want using other methods.
Here is your file, in which the value of cell E1 of Sheet2 changes automatically depending on the active cell.
RealCELL.ods (15.6 KB)

#20

Thanks for your endeavours. My bad, I failed to mention that I won’t ever use macros in sheets for the simple reason that they eviscerate the undo stack and are of little use to any user who ever makes a mistake. My “real” sheet was helping the user maintain a number of variable over a range of projects with a number of entries per day per project. The ability to locate and display the project “header” data above the “moving targets” assisted accuracy and permitted the user to “go back and change” any of the variables. One mistake, UNDO, Sorry Chump - No Can Do

#21

Look closely, this is Calc, not Excel. :slight_smile:

#22

Moreover, we can set up the macro in such a way that it will write to the undo stack only those actions that we specify. For example, in our case, it will not write to the undo stack the action of changing cell E1.
Calc’s undo stack management is incomparably more powerful than Excel’s.

#23

I know one use case for that function: =CELL("filename")
For anything else, there are dedicated functions like SHEET( [ref] ); ROW( [ref] ), COLUMN( [ref] ), ADDRESS(row;col), TYPE(ref).

#24

This topic is about tracking the current (active) cell of a sheet using formulas. In Excel, this feature is provided by the CELL function with the second parameter omitted.

#25

I’ll be honest, when Calc told me it was a sheet with a macro I didn’t even bother to open it because I wasn’t aware a macro could be written that didn’t affect the stack. I just tried it. Thank You for the solution

1 Like
#2

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.

#3

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.

#7

tdf#106151

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.