I learned something today. I “knew” that omitting the optional reference argument from the CELL worksheet function meant that the cell that contained the worksheet function was used. Patrick McDonald correctly pointed out that it uses the last changed cell, not the host cell.
In a new worksheet, type =CELL("width") in cell B3. If you’re using Excel 2003 like me, you’ll probably get 8 as the answer. Now increase the width of column D and type a ‘1’ in D3. Cell B3 will report the width of D3 because it’s the last cell changed.
I like learning things. I don’t like learning things that I should have already known. Hopefully this is the last one and that now I know everything.
I tried typing in =CELL(“address”). Now I get the address of the last cell I changed. That could be useful.
Robert Hopkins beat me to it and, yes, that’s an interesting thing to know…
Anyone care to offer examples?
No, it is not the last changed cell. Excel help is wrong.
Leaving out the cell reference in the CELL formula results in it referring to the *active* cell.
Try this.
In A1 enter =CELL(“width”). It will show 8 in Excel 2010.
Now, resize D. A1 will still show 8.
Change D1. Now, A1 will show the new width of D.
Select E1. CTRL+SHIFT+ALT+F9. Now, A1 will show 8.
Here’s another test.
Enter =CELL(“address”) in say, A10. A10 will now show $A$10.
Select E8. CTRL+SHIFT+ALT+F9. Now, A1 will show $E$8.
Or,
Save the workbook — say, it’s called Book2.xlsx.
Now, enter =CELL(“address”) in say A10. A10 will contain $A$10.
Now, switch to Sheet2. CTRL+SHIFT+ALT+F9. Switch to Sheet1.
A10 will contain [Book2.xlsx]Sheet2!$A$1
Getting REALLY PICKY, it returns the property of the active cell at the time of the last recalc.
Do Excel 2007 and 2010 (and 2008 for Macs) require CSA+F9? Excel 2003 updates cells with volatile function calls when just pressing plain F9.
Lotus 1-2-3’s @CELLPOINTER function was useful in old style 1-2-3 macros. Since Excel circa 1987 had a limited ability to run such 1-2-3 macros, it needed an equivalent function. But ever since MSFT dropped support for such macros from Excel there’s been no need/sensible use for CELL without a reference argument.
I feel like I’m back in the 90’s. :)
Dick, welcome back from whatever you’ve been doing!
Tushar
Regarding this…
It will show 8 but that width is not of A1 but of E1.
I got the width of E1 and not of A1. I did this in Excel 2003.
In A1 enter =CELL(“width”) >> A1 Showed 8
Resize D >> A1 Showed 8
Selected E1 and resized it. Pressed CTRL+SHIFT+ALT+F9 >> A1 showed 23.
Sid
Until a few days ago, I saw no real value in using CELL() without the 2nd argument. Now, I do. {grin}
http://www.dailydoseofexcel.com/archives/2011/08/10/highlight-matches-to-the-current-cell/
Siddharth,
Yes, I know. That was the idea. It shows the width of E1, the *active* cell at the time of the recalcuation.