reference to range names

[Duane]

Hi,

I have a drop down box that refers to named ranges. Depending on the name
that is picked in the drop down, I need to retrieve data from a named range.
The range will change and I need to refer to the new range. The formula I am
using is like this:

=VLOOKUP(B5,range,3,FALSE)

I want to change the "range" to a cell link where the name of the range I
want to use will be....which will change based on user selection.

Hope this makes sense.

 

[Miguel Zapico]

You can write the name of the range in a cell (say A1) and change the formula
to:
=VLOOKUP(B5,INDIRECT(A1),3,FALSE)
Changing the value of A1 between your valid names will change the range you
are looking at in the formula.

Hope this helps,
Miguel.

 

[Duane]

Thanks. That was exactly what I needed.

You can write the name of the range in a cell (say A1) and change the formula
to:
=VLOOKUP(B5,INDIRECT(A1),3,FALSE)
Changing the value of A1 between your valid names will change the range you
are looking at in the formula.

Hope this helps,
Miguel.

 

[Bob Phillips]

Use a list of ranges, like M1:N10,O110, etc. and a formula of

=VLOOKUP(B5,INDIRECT(D1),2,FALSE)

where D1 is the DV cell

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)