RETURN SELECTED CELL NAME FORMULA

[ndendrinos]

Is there a formula that will return in A1 the selected cell name in range B1:B10
example : If I click in B2 I will read in A1 the word "B2"

Easy to do with a message box but a formula?
I do not want the user to have to accept macros when he or she opens the file

Thank you

Edit: If easier I could type in the cells their name and have the formula in A1 return the selected cell's "VALUE"

example In B1 I would type B1 etc...

 

[ravishankar]

Hi
if you type =b1 in A1 it will return the value in B1.
if you type =indirect(b1) and c5 in B1 it will return the value of c5 in A1.You have to choose which cell value you need in A1. we need some more information, otherthan that it is an active cell in B1: B10 range to choose from
Ravi

 

[ndendrinos]

Thank you Ravi,
I cannot give more details because there aren't any to give really...
But I can demonstrate this with this code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Selection.SpecialCells(xlCellTypeVisible).Select
For Each a In Selection
MsgBox "You have chosen " + a.Value


Next

End Sub

You see I don't know what cell in the range will be selected ... can do it with the above code BUT need a formula instead if possible.

 

[Peter_SSs]

Is there a formula that will return in A1 the selected cell name in range B1:B10
example : If I click in B2 I will read in A1 the word "B2"

Easy to do with a message box but a formula?
I do not want the user to have to accept macros when he or she opens the file

Thank you

Edit: If easier I could type in the cells their name and have the formula in A1 return the selected cell's "VALUE"

example In B1 I would type B1 etc...

I may be proved wrong, but I don't think that you can do this without vba. Can you tell us why you want this formula and what you are going to do with the result in A1?

Note that when you select any cell, its address appears in the "Name Box" which is just above A1. Is that any use?

 

[ndendrinos]

Hello Peter and thank you for your reply.
The reason I need this is that I click through a picture and the grid behind it is hidden by the picture.
As for the address appearing in the "Name Box" which is just above A1 it is not appliquable as I intend to hide that too

What I need is just the picture and one visible cell telling me what cell is selected at any given time.

 

[Peter_SSs]

Hello Peter and thank you for your reply.
The reason I need this is that I click through a picture and the grid behind it is hidden by the picture.
As for the address appearing in the "Name Box" which is just above A1 it is not appliquable as I intend to hide that too

What I need is just the picture and one visible cell telling me what cell is selected at any given time.

Then I think you would have to allow macros.

 

[ndendrinos]

Too bad but so be it then
Regards.
Nick

 

[mrwan]

hi, try this


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if activecell.address <> "$A$1" then range("A1") = activecell.address
End Sub

 

[ndendrinos]

Thank you mrwan for the code

 

[erik.van.geit]

Hi,

don't you need your UNDO-function ?

normal sheet
type "anything" in B1
ENTER
hit Control+Z "anything" is erased again

now add the code to your sheetmodule and try again: that's too bad

workaround
create new sheet, name it "hidden" and hide it
in A1 of your sheet type =hidden!A1
code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Address <> "$A$1" Then Sheets("hidden").Range("A1") = ActiveCell.Address
End Sub

kind regards,
Erik