Vlookup and named ranges

[Master_G]

I have a number of named ranges and am trying to use a drop down box combined with a vlookup to pull data from them.

So if the ranges are "Test1", "Test2" and "Test3", the drop down box is cell C1, my vlookup is:

=VLOOKUP(A2,INDIRECT(C1),3)

This gives me a #REF! error.

In testing this I have put "Test1" in place for INDIRECT(C1) and this works, I have tried a named range on the same sheet and that works, but the named ranges referring to other sheets just REF out.

Any ideas?

G

 

[Jonmo1]

Welcome to the board...

How are the named ranges defined?

If the named ranges are "dynamic", using offset or something of the like..
Then it won't work with Indirect.

To use named ranges in Indirect, those named ranges must be Static.

 

[Master_G]

Ah, yes they are dynamic named ranges which would explain why it won't work.

In that case, is there any way I could achieve this?

G

 

[Jonmo1]

Let's talk about REAL situation, not example Test1 Test2 etc...

How many named ranges are there, and what are the actual names?

You might do something like

VLOOKUP(A2,IF(C1="Test1",Test1,IF(C1="Test2",Test2)),3)

 

[Master_G]

If at all possible I would like to avoid solving it through nesting - there will only be 6 named ranges at the moment but this number will increase as the sheet is used and I would like to make this sheet as dynamic as possible.

At the moment the actual ranges are Test1, Test2, Test3!

This will change at the final revision when I drop the real data in but at the moment am just using non-real datasets to test the sheet as the real datasets are pretty big and clumsy to test with.

G

 

[Jonmo1]

I think your only options are...

1. Nesting the ranges per previous example (other methods avaliable too)
2. Use Static Ranges, but oversize them by 10% or so to accomidate fluxuating data.
3. Use VBA Scripts to ReCreate the named ranges on the fly as the data is updated.

 

[Master_G]

Thanks for your replies, I think for the moment I will go down the static range route, my understanding is the ranges shouldn't change too much so I will see if that is acceptable to the users, if not I will go down the macro route.

Many many thanks for your help

G