How to get the cell reference of the value selected from name list

[YY san.]

Hi,
I have a name list range called Workdays in Sheet2 consists of name range
values A1~A5 = Monday ~ Friday.
In Sheet1, colum A, I have a data validation referring to this list.
If in cell A1, Friday is selected. I want to show "A5" in B2 of Sheet1. Can
anyone help?
Thanks so much for your help.

 

[francis]

try this in B2 and your lookup value in B1

=ADDRESS(MATCH(B1,A1:A5,0),1,4)

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

 

[T. Valko]

There are several ways to do this. This one works no matter where your named
range Workdays is located.

Array entered** :

=ADDRESS(MAX((Workdays=A1)*ROW(Workdays)),COLUMN(Workdays),4)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

P.S.

In other words, I'm assuming you want the *specific* cell address that
matches the selection from the drop down list.