formula to display current active cell in $A$1

Hello friends,
While I know that Address(Row(),Column()) will display the cell address where it is entered, I need to put a similar formula in $A$1 that will list the current active cell.
For example, if I select B3 then $B$3 would appear in $A$1. If I next select E8, then $E$8 would appear in $A$1.
Is this even possible? If so, please share your knowledge with me.
Thanks kindly!

Thanks so much for the quick response KOKOSEK - unfortunately, it doesn't do anything.
I entered the VBA and saved it (and opened it again to confirm it's still there). Am I supposed to do something to make it run? I'm hoping it will automatically change the value in A1 every time a different cell is selected. Thanks again.

you could use
=CELL("address") in cell A1

this only will update though if you double click on another cell or select a cell and hit F2 and enter

kosokek code is fine. You probably put it in the wrong place.

The code he gave is an event macro that needs to be placed in the sheet module that you want this to happen (to show activecell in A1)

please see attached example for the correct location of code (and that it does work)

Thanks for the attachment Roel. You are obviously correct, it does work. It looks identical to the one in my module that doesn't work - I'll figure it out. Have a great day!

Hello again guys . . . the help provided does work and I thank you.
Unfortunately, I'm unable to use it as needed because I'm just not quite smart enough to figure out how.
I have tried to attach the spreadsheet from Roel that I edited in my attempt to answer the primary question, but I must not have permission to do so. It allowed me to upload it but when I press the attach button it does nothing.
The ultimate goal is to enter a formula on Sheet2 to retrieve the value of the active cell on Sheet1.
Since

Please Login or Register  to view this content.

identifies the address of the active cell on Sheet1, the formula in Sheet2 would look at

Please Login or Register  to view this content.

and goto the address displayed and return whatever value is in that active cell.
I tried:

Please Login or Register  to view this content.

but it only gives me the address of the active cell $B$4.
I tried

Please Login or Register  to view this content.

and get a #REF! error. I'm baffled.
Just to test, I entered

Please Login or Register  to view this content.

and it gives me that value I need, "GregM".
Again, all help is greatly appreciated.

Okay, so I figure out that [code]INDIRECT("'"&A6&"'!"&INDIRECT("'"&A6&"'!"&"A1")){/code] will work for the problem. Now I'll go to a new post to ask for help
to be able to replace A6 in this formula with "Sheet1" (I put in the word Sheet1 in A6).
This original question is SOLVED. Thanks to all who helped!

Hi Roel,

I already have another code in the sheet and I want to add this too.. Can you help me please?

My existing code is

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = 3 And Target.Column = 6 Then

Application.EnableEvents = False

Sheets("List").Range("F4").Value = [=IFERROR(VLOOKUP($F$3,Data!$A$1:$C$7,2,0),"")]

Sheets("List").Range("F5").Value = [=IFERROR(VLOOKUP($F$3,Data!$A$1:$C$7,3,0),"")]


End If

Application.EnableEvents = True


End Sub

Thanks in advance.