Use a range name based on the worksheet name

[tmanya]

Excel 2007, SP2

In a VLOOKUP formula, I want to use a range name for the table/array reference; however, the range referenced will change depending on the name of the worksheet in which I am entering the formula.

Worksheets:
PP_HMLV
PP_SCS
PP_Auto

Range Names:
hmlv
scs
auto
(the named ranges are on their own worksheets named the same as the range)

The format of each named range is the same, row 1 contains headings and there are varying data records from range to range and day to day. The range would be from $A$1:$F$???. I would prefer to use a dynamic range [ offset($A$1,0,0,counta($A:$A),counta($1:$1)) ] but understand that sometimes those don’t work with certain formulas, although they have worked with VLOOKUPs before.

Active Worksheet name: PP_HMLV
Range name to reference: hmlv

Active Worksheet name: PP_SCS
Range name to reference: scs

Formula with range name (returns correctly)
=VLOOKUP($B3,hmlv,LEFT(E$2,1)+1,FALSE)

Formula with range name reference
=VLOOKUP($B3,RIGHT(RIGHT(CELL("filename",A2),LEN(CELL("filename",A2))-SEARCH("]",CELL("filename",A2))),LEN(CELL("filename",A2))-SEARCH("_",CELL("filename",A2))),LEFT(E$2,1)+1,FALSE)

The part in green above returns HMLV as a formula by itself but I want but I do not know how to get Excel to understand that as a range name.

I tried using INDIRECT but I don’t have a sufficient understanding of that function to manipulate it.

I ultimately want to use the same formula across the three worksheets without having to do a find/replace on the range name.

Any advice is greatly appreciated.

Tanya

 

[AlphaFrog]

Try something like this to reference the named range based on the sheet name...
INDIRECT(TRIM(RIGHT(SUBSTITUTE(CELL("filename",A2), "_",REPT(" ",100)),100)))

Or you could put the name of the range on each sheet in the same cell (say cell A1 for this example) and just use...
INDIRECT(A1)
...to reference the named range in cell A1.

 

[tmanya]

VLOOKUP($B3,INDIRECT($A$1),LEFT(E$2,1)+1,FALSE)

returns #REF!

I'll give the 'SUBSTITUTE' solution a try

 

[AlphaFrog]

If this formula works...
=VLOOKUP($B3,hmlv,LEFT(E$2,1)+1,FALSE)

...And you have hmlv (no quotes) in cell A1, then this formula will work. I tested it.
=VLOOKUP($B3,INDIRECT(A1),LEFT(E$2,1)+1,FALSE)

 

[tmanya]

If this formula works...
=VLOOKUP($B3,hmlv,LEFT(E$2,1)+1,FALSE)

And you have hmlv (no quotes) in cell A1, Then this formula will work. I tested it.
=VLOOKUP($B3,INDIRECT(A1),LEFT(E$2,1)+1,FALSE)

I expected it to work as well and it was one of the things I tried before posting... but it's not and I'm getting a #REF! error when I use it.
=VLOOKUP($B3,INDIRECT(A1),LEFT(E$2,1)+1,FALSE)

No quote, no extra spaces, no hidden characters, absolute reference, relative reference and on a lookup that I KNOW will return a value... hence my frustration...

 

[tmanya]

Got it...

I had changed my named ranges from static to dynamic... thought I had changed them back but apparently hadn't. I get the correct result when the ranges are static...

phew...

Thank you very much for your help!

 

[AlphaFrog]

You're welcome and thanks for the feedback. That would have bugged me to not know why it didn't work.