Table Array in VLOOKUP Relies on Data Validation

[willydlish]

I apologize if this has already been addressed somewhere else, but I
could not find it....

I have a timesheet that I'm creating that relies heavily on data
validation driving drop-down lists. I've created ranges for all the
possible results of the drop-down choices, however, when I want to
query additional information in a named range through VLOOKUP, I cannot
use the result of the drop-down choice as the valid range. Despite the
cell displaying the range name it cannot pass this to VLOOKUP without
giving me an error.

Does anyone know how I can take the displayed result in a drop down box
and use this to fuel the table array for a VLOOKUP...in a worksheet
function !?!

Willy D.

 

[Biff]

Hi!

You can use Indirect provided it's a static named range.
If it's a dynamic range it won't work.

Assume the dropdown is in C1:

=VLOOKUP(A1,INDIRECT(C1),2,0)

Biff

 

[willydlish]

It is a static name range...many thanks Biff.

Ya know I played with INDIRECT a zillion times but completely blanked
on this possibility.

Cheers.