Hi,

I have a dropdown from a validation list. I want to reference the cell that
the list is pointing to.

Ex. my list where the dropdown is located is E71, the list is located at
AC69 and is 23 rows. When an item is selected from the list, I want to
reference the cell in the list (AC69-AC91) not E71.
--
TIA

AFJr

I want to reference the cell in the list (AC69-AC91) not E71.

For what purpose? Does this mean you want the cell address? Do you want to
use the address as a reference in a formula?

--
Biff
Microsoft Excel MVP


"AFJr" wrote in message
...
Hi,

I have a dropdown from a validation list. I want to reference the cell
that
the list is pointing to.

Ex. my list where the dropdown is located is E71, the list is located at
AC69 and is 23 rows. When an item is selected from the list, I want to
reference the cell in the list (AC69-AC91) not E71.
--
TIA

AFJr

Yes I want to use the cell address as a reference for a formula.

Thanks for taking the time to respond.
--
TIA

AFJr


"T. Valko" wrote:

I want to reference the cell in the list (AC69-AC91) not E71.

For what purpose? Does this mean you want the cell address? Do you want to
use the address as a reference in a formula?

--
Biff
Microsoft Excel MVP


"AFJr" wrote in message
...
Hi,

I have a dropdown from a validation list. I want to reference the cell
that
the list is pointing to.

Ex. my list where the dropdown is located is E71, the list is located at
AC69 and is 23 rows. When an item is selected from the list, I want to
reference the cell in the list (AC69-AC91) not E71.
--
TIA

AFJr

Depending on how robust this needs to be, try one of these:

Robust version

Arrary entered**

=ADDRESS(MAX((AC69:AC91=E71)*ROW(AC69:AC91)),COLUM N(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:AC9 1)),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" wrote in message
...
Yes I want to use the cell address as a reference for a formula.

Thanks for taking the time to respond.
--
TIA

AFJr


"T. Valko" wrote:

I want to reference the cell in the list (AC69-AC91) not E71.

For what purpose? Does this mean you want the cell address? Do you want
to
use the address as a reference in a formula?

--
Biff
Microsoft Excel MVP


"AFJr" wrote in message
...
Hi,

I have a dropdown from a validation list. I want to reference the cell
that
the list is pointing to.

Ex. my list where the dropdown is located is E71, the list is located
at
AC69 and is 23 rows. When an item is selected from the list, I want to
reference the cell in the list (AC69-AC91) not E71.
--
TIA

AFJr