Refer indirectly to named range

**chartguy** · 05-15-2009, 05:30 PM

Is it possible to refer indirectly to named ranges?

I have a workbook with over 30 named ranges. On one of the sheets, I have a column listing the named ranges. The adjacent column uses a VLOOKUP to search those ranges. Unfortunately, I do not seem to be able to use a formula to refer to the named range.

Say cell B3 contains the string, MOTtext (the name of the named range).
if C3 contains
=VLOOKUP(A3,MOTtext,5,true)
it will work, but I'd like to be able to use something like
=VLOOKUP(A3,B3,5,true) to refer to the name

Any ideas? I've tried INDIRECT() and spelling out the range (Book.xls!MOTtext), but neither seems to work.

**shg** · 05-15-2009, 06:17 PM

Welcome to the forum.

=VLOOKUP(A3, INDIRECT(B3), 5, TRUE)

If the name in B3 has sheet scope on another worksheet, it should appear in B3 as, for example, Sheet2!MOTtext

INDIRECT is volatile, so you'll want to use it sparingly.

**chartguy** · 05-16-2009, 12:47 PM

Thank you. I had tried that, but referring to the full name Book.xls!MOTtext in another cell would not work. With you're prodding, I tried just MOTtext, and it worked. I must have done something wrong in the way that I specified the full range. Anyway, I can now use the formula in a column with 33 named ranges.

It's important to me, because a FSO VBA routine deletes the old sheets and imports the (updated) data for those 33 ranges (all the CSV files in a folder), and creates the named ranges from the file names. This way, if they change (e.g., a 34th CSV file is added), I don't have to re-write everything.

Thanks,
jc the cg