Referencing Multiple Worksheets using VLOOKUP and INDIRECT

[Harry Flashman]

I am trying to use INDIRECT to refer to a range on a given worksheet
but am getting mixed results. Would some please inspect these formulas
and tell me what I am doing wrong?

Sheet1 contains a range; I would like to lookup a value in that range
from another worksheet.

This example works:
If the value in B1 is 1, then the following formula returns the
correct value
=VLOOKUP($A4,INDIRECT("Sheet"&B1&"!$A$1:$C$3"),2,0)

This example does not work:
But if the value in B1 is Sheet1 the the following formula returns
#REF!
=VLOOKUP($A4,INDIRECT(B1&"!$A$1:$C$3"),2,0)

In real life my worksheets will not have names like Sheet1, Sheet2 but
rather words like Region and Media etc
In the past, I have managed to get around this by naming the range on
each worksheet, and then using INDIRECT with cells containing the name
of my range, but this time I wanted to try something different.

I would be very appreciative if someone could guide me here. Thank you.

 

[David Heaton]

I am trying to use INDIRECT to refer to a range on a given worksheet
but am getting mixed results. Would some please inspect these formulas
and tell me what I am doing wrong?

Sheet1 contains a range; I would like to lookup a value in that range
from another worksheet.

This example works:
If the value in B1 is 1, then the following formula returns the
correct value
=VLOOKUP($A4,INDIRECT("Sheet"&B1&"!$A$1:$C$3"),2,0)

This example does not work:
But if the value in  B1 is Sheet1 the the following formula returns
#REF!
=VLOOKUP($A4,INDIRECT(B1&"!$A$1:$C$3"),2,0)

In real life my worksheets will not have names like Sheet1, Sheet2 but
rather words like Region and Media etc
In the past, I have managed to get around this by naming the range on
each worksheet, and then using INDIRECT with cells containing the name
of my range, but this time I wanted to try something different.

I would be very appreciative if someone could guide me here. Thank you.

Harry,

I have tried both your formulas and they both work fine.

The only time i could emulate the #REF error was if the Sheet
reference was wrong. You could try renaming your sheet and trying
again.

Regards

David

 

[Harry Flashman]

Harry,

I have tried both your formulas and they both work fine.

The only time i could emulate the #REF error was if the Sheet
reference was wrong.  You could try renaming your sheet and trying
again.

Regards

David- Hide quoted text -

- Show quoted text -

Indeed they do both work fine. The workbook I was testing this on was
unsaved, actually. Regardless I tried the same thing today and it
worked. Funny thing is I copied my formulas directly from the workbook
yesterday to make my post (when it appeared not to be working), and
then today I copied back to my workbook (albeit newly created one for
testing purposes). There must have been some unnoticed factor
yesterday that was thwarting my endevour, which made me think my
syntax was somehow not on the mark. No matter, this is great. Thanks
very much for looking into. Cheers.

 

[Harry Flashman]

Sorry David, I misread part of your post - I read rename workbook,
when you wrote rename worksheet. Yes that was most likely the problem
- the worksheet name was wrong.