Depending on how robust this needs to be, try one of these:
Robust version
Arrary entered**
=ADDRESS(MAX((AC69:AC91=E71)*ROW(AC69:AC91)),COLUMN(AC69:AC91),4)
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
Not so robust version
Normally entered
="AC"&MATCH(E71,AC69:AC91,0)+68
Both will return a *TEXT* string in the form of a relative cell address.
However, as is, it can't be used as a reference in a formula. You would need
to wrap these formulas inside an INDIRECT function like so:
=INDIRECT(ADDRESS(MAX((AC69:AC91=E71)*ROW(AC69:AC91)),COLUMN(AC69:AC91),4))
=INDIRECT("AC"&MATCH(E71,AC69:AC91,0)+68)
This has the net effect of using the value at the cell address as a
reference but you'll get the same thing if you just reference the drop down
cell!!!!!
For example:
E71 drop down = Joe
Joe is located in the source list at address AC89
Without the use of INDIRECT both formulas will return the *TEXT* string
AC89. To make this a usable cell address we include the INDIRECT function.
Then both formulas will return Joe. So, in essence we took the long way
around to return Joe when all we needed to do was reference the drop down
cell E71.
--
Biff
Microsoft Excel MVP
AFJr said:
Yes I want to use the cell address as a reference for a formula.
Thanks for taking the time to respond.