H
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?
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 value returns
#REF!
=VLOOKUP($A4,INDIRECT(B1&"!$A$1:$C$3"),2,0)
In real life my worksheet will not have names like Sheet1, Sheet2 but
rather words like Region and Media etc
I have manage to get around this in the past by naming the range on
each worksheet, but this time I wanted to try something different.
I would be very appreciative if someone could guide me here. Thank you.
but am getting mixed results. Would some please inspect these formulas
and tell me what I am doing wrong?
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 value returns
#REF!
=VLOOKUP($A4,INDIRECT(B1&"!$A$1:$C$3"),2,0)
In real life my worksheet will not have names like Sheet1, Sheet2 but
rather words like Region and Media etc
I have manage to get around this in the past by naming the range on
each worksheet, but this time I wanted to try something different.
I would be very appreciative if someone could guide me here. Thank you.