Variable range name in a formula

[DENWARD]

=VLOOKUP(B2,'10_28_2012 MFC PROJECT V1.xltm'!lower1,6,FALSE)

I need to change the vlookup range name (i.e. "lower1" ) based on contents of an adjacent cell B1.

I surely appreciate any assistance you can give me.
Thanks
den

 

[Smitty]

Welcome to the Board!

Take a look at INDIRECT.EXT, which is part of the MOREFUNC add-in.

HTH,

 

[Andrew Poulsom]

Since the source workbook seems to be open try:

=VLOOKUP(B2,INDIRECT("'10_28_2012 MFC PROJECT V1.xltm'!"&B1),6,FALSE)

 

[DENWARD]

I think this worked well but now get a ding on the range name-Are there any restrictions on creating a range name?

 

[Andrew Poulsom]

The syntax rules are here:

Define and use names in formulas - Excel - Office.com