Forum Discussion
Formula to get address of selected cell on a worksheet
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.
Googled a bit and fount it in this Formula that returns address of the active cell. | MrExcel Message Board year 2009 discussion. Used that few years ago, that's why remember. However not very suitable .
