named ranges in VLOOKUP

[KKop]

I am having a problem with named ranges in the VLOOKUP function.

Here's what I am trying to do:

I have a main sheet (Sheet1) that contains a table. This table is populated from one of the other sheets in the workbook through a VLOOKUP function.

At first I didn't have named ranges, and used the INDIRECT function to look up (in cell E4) what sheet to look the data up on (for instance: "Sheet3"):

VLOOKUP($C11, INDIRECT($E$4 & "!$C$5:$H$116"), 2, FALSE)

I later decided to use named dynamic ranges so I wouldn't have to change the range reference ($C$5:$H$116) in the formula on the main sheet if the table size on Sheet3 changed in the future.

I created a dynamic named range on Sheet3 (named "Sheet3TestResults"), and changed the formula to:

VLOOKUP($C11, INDIRECT($E$4 & "TestResults"), 2, FALSE)

This way (I thought), I'd be able to change the value in E4 (from Sheet3 to Sheet4, etc), and be able to use the same formula to look up the values.

However, I can't get the formula to work this way. As quoted above, it returns a #REF.

Suggestions please! Either on getting the formula to work, on for another way to point to dynamic named ranges on other sheets.

Thanks!

[martindwilson]

works for me

[DonkeyOte]

INDIRECT won't work with Dynamic Named Ranges

Martin yours works because your Named Ranges are not Dynamic
(ie they are hardwired ranges)

You may be able to make the range dynamic within the formula itself (ie not using DNR) but we'd need to see an example.

Using Martin's file as an example... dispensing with Named Ranges (ie we assume they will be Dynamic and thus won't work), then you could try an approach such as:

Sheet1!A1: =VLOOKUP(C11,INDIRECT("'"&E4&"'!A1:B"&MATCH(REPT("Z",255),INDIRECT("'"&E4&"'!B:B"))),2,0)

[martindwilson]

ah ah never noticed that ,i am getting old!