=CELL("address") works in some worksheets but not in others

[edge37]

Hi! This simple formula to return the address of a cell still works on a worksheet I created a few years ago. Now in the file where I need it, it simply doesn't work, I click a cell and the cell address do not change or update; it only works if I press F9 after or if I enter anything in a cell and then click on another empty cell (weird) and I need this to work automatically. Maybe you can help me solve this riddle I have. I'm using Excel 2021, I include some images to show what I mean.

Thank you

 

[MARK858]

What happens when you anchor the formula to a cell reference i.e.

=CELL("address",A1)

Is you calculation still set as Automatic?
Do you have any circular references?

 

[edge37]

What happens when you anchor the formula to a cell reference i.e.

=CELL("address",A1)

Is you calculation still set as Automatic?
Do you have any circular references?

I get $A$1, which is what it is expected.

 

[MARK858]

...and the other 2 questions?

 

[edge37]

...and the other 2 questions?

Sorry yes, automatic calculations already set
No circular references

 

[edge37]

UPDATE
It seems that this formula works only with a VBA code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("AA6").Value = ActiveCell.Value
End Sub

Don't know why why this happens, but the cell address formula works fine when that code is inserted in the worksheet. Thank you for your help

 

[MARK858]

Thank you for the follow-up