Forum Discussion
Formula to get address of selected cell on a worksheet
Hi,
I'm looking for a formula to get the currently selected cell within a worksheet.
- No VBA or macros since my client has that disabled.
- Not the last cell edited since an edit may not occur.
- It can't be affected by changes of selection on other worksheets.
This is obviously information that Excel tracks and there is no good reason why it wouldn't be readily available. (I know there is always the possibility of bad reasons they may keep this information inaccessible, but I'm hoping that isn't the case.)
Any help would be appreciated.
Thank you,
John
10 Replies
Close solution is to use in formula INDIRECT( CELL("address") ), but you need to press F9 or recalculate by other way every time when another cell is selected.
- Unfortunately, CELL("address") will only give the most recent edited cell and not the selected cell.
We speak about INDIRECT( CELL("address") ). After the refresh (F9) it returns the value from the selected cell, aka cell in focus. Doesn't matter it is last edited or not, any focused cell.
If we are on last edited cell when F9 is not required, it is in focus by default.
And yes, without F9 it doesn't work with selected cell.
Essentially, you're looking to display the address that the name box shows. For example, let's say the formula to return the address of the active cell is in A1 and the active cell is Z100. The name box shows Z100 so the formula in A1 would return "Z100".
There's a few obstacles:
- theoretically, a formula capable of doing this would need to recalculate on selection change. A formula that's somewhat related to this is:=INFO("ORIGIN")The above cell returns the top-leftmost visible cell on the sheet.
Scroll down/scroll up, one must recalculate the results to get the origin.
The re-calc part is not the deal breaker though. The big obstacle is CELL and INFO cannot determine the active cell unless you want to return the address of the cell you're in. I believe you'd need vba to accomplish this task.
- Thank you. That's what I've found so far as well. I was just hoping the infinite brain trust in this group might have some other ideas.
Very well explained, Patrick2788
As an addition, in case jhbrooks is just looking to return the address of the cell he/she's in, it is simply using:
=CELL("address")But as perfectly explained by Patrick, this is something not "dynamic". It will simply return the reference of the cell wherever this formula is written and be left there, but maybe that is what you want...
What I meant - define the name and use it in formula as
Initially that will be circular reference. Now select any cell and press F9
Select another cell and F9
That's many years old solution, if we may consider it as solution. Perhaps something else exists except VBA, but I don't know.
