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

  • Thread starter Thread starter YY san.
  • Start date Start date
Y

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.
 
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
 
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.
 
P.S.

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