Automating Name Range

[alphanov]

Hi

This is my first post on here so please be gentle with me!

I am attempting to do the following:

I have several drop down boxes (using the data validation - list method) where the sheet user can select data from a restricted range.

I want to be able to then use those selections to select the correct named range of data to perform a VLookup() with.

For example:
I have "Terrain", "Time", "Frequency" which can be attributed values from three lists. So if the user selected, in the same order, Land,50,100 this should select the named range Land50_100MHz so that I can then perform a VLookup() based on other criteria.

I used Concatenate() to get the selections into a single string (Land50_100MHz) but when I use that cell in VLookup() I just get a '#N/A' message. I have also "named" the cell with Concatenate() in and tried that in VLookup() with the same result.

Any suggestions please?

##Forgot to say... I'm using Excel 2002 upwards and need it to be compatible with the older version...

Thanks in anticipation.

-
Nev

 

[Richard Schollar]

Hi Nev

Assuming these named ranges are static and not the result of a dynamic formula, then you can use INDIRECT:

=VLOOKUP(Lookup_Value,INDIRECT(A1&A2&"_"&A3&"MHz"),Col_Ref,FALSE)

Assuming A1:A3 hold your "Land", "50" and "100" values respectively.

 

[alphanov]

Richard

WOW!!!!

Thanks that has worked... I've never come across the INDIRECT() function before!

Many grateful thanks...

Best regards

--
Nev