CELL function

[Lewiy]

This week I received my latest free chapter of Learn Excel from Mr Excel (Chapter 11). On page 220 there is a solution to a lookup problem which reads:

=VLOOKUP($F28,$F$23:$H$26,CELL("Col",G28)-5,FALSE)

I was curious as to the use of CELL("Col",G28) when surely COL(G28) would work just the same and is more concise.

I must say, I've never found much use for the CELL function (I'm sure it has it's uses ) and it seems odd that some of the features of the function overlap with other functions, namely:
"col", "row", "contents"

When there would appear to be much better ways to obtain the information required.

Does anyone actually use the CELL function on a regular basis or is it just there to look pretty?

 

[Andrew Fergus]

Hi

In response to this:

Does anyone actually use the CELL function on a regular basis or is it just there to look pretty?

Yes - I use it frequently like this to get the spreadsheet name and location, like this:
=CELL("filename", A1) {or =CELL("filename")}

or like this to return a worksheet name :
=RIGHT(CELL("Filename",A1), LEN(CELL("Filename",A1)) - SEARCH("]",CELL("Filename",A1)))

I recently had to use a variation of this with an indirect formula to retrieve values from another worksheet where the worksheet name was changing - it worked very nicely. So in conclusion I think it does a lot more than just look pretty

Cheers
Andrew

 

[TinaP]

I don't use the cell function very often, but when I need it, I'm glad it's there.

 

[Darren Bartrup]

As Andrew said, I use it regularly when using the INDIRECT formula.

I started doing this after creating a formula for someone at work, who then went and changed the worksheet name just before trying to showcase his new all singing-all-dancing spreadsheet. Of course, the formula didn't work.

 

[UniMord]

In answer to Lewiy's question regarding CELL's redundancy, it is redundant, but it has a purpose. Help has this to say:

The CELL function is provided for compatibility with other spreadsheet programs.

That said,

=CELL("contents", range)

is good for getting the first item in a dynamic or relative range.

 

[Lewiy]

The CELL function is provided for compatibility with other spreadsheet programs.

Well spotted, thanks!

 

[pgc01]

In answer to Lewiy's question regarding CELL's redundancy, it is redundant, but it has a purpose.

I also use Cell to get the name of the worksheet/workbook.

Is it redundant? How else do you get the worksheet name with just a formula (without vba or some old excel4 macro)?

Kind regards
PGC

 

[UniMord]

PGC, we agree that the CELL worksheet function has its useful features. However, LEWIY observed:

it seems odd that some of the features of the function overlap with other functions, namely: "col", "row", "contents".

These features are clearly redundant. The COLUMN, ROW functions do the job of "col" & "row" more easily and directly. Now that I mention it, I realize that my suggested use for "contents" adds nothing that INDEX can't do.

 

[pgc01]

Hi

I'm sorry if I was not clear. I meant that Cell() is not redundant as a whole, because of "filename". I agree that some features are redundant. You give 3 good examples, I'd add Address. And other features may not be redundant but the truth is that I never felt the need to use them.

Best regards
PGC

 

[Scott Huish]

I've actually used CELL("address" in several answers on this board.

It's useful if you are wanting to return the address for where it found something. Say you want to return the address for the first value of 9 in the range of A7:A12:

=CELL("address",INDEX(A7:A12,MATCH(9,A7:A12,0)))