Convert text string to lookup array

[jmt73]

Hi,
I want to be lazy - I've got a budget template that is the same format across several worksheets, but the actual numbers change.

I want to reference the current budget template just by typing in the name of the worksheet it is in, and a vlookup populates the reconciliation sheet.

So using ADDRESS(), I can create either side of the array.
Then with TEXT(), I can turn it into a text "array".
How can I convert that text "array" and put it into a VLOOKUP() as a search array?

ie cell C1 is where I have entered "First Budget", which is the name of the corresponding worksheet.
ADDRESS(5,1,1,1,C1) returns 'First Budget'!$A$5
ADDRESS(88,8,1,1,C1) returns 'First Budget'!$H$88, which bookends the array

I've been using TEXT() to combine them and give:
'First Budget'!$A$5:'First Budget'!$H$88

What I really want to do is use that as the search array in VLOOKUP, so when the budget changes, I just need to type in the name of the relevant worksheet to update it.

PS, I know that by definition a budget shouldn't change very often, but believe me, they can and do in my little part of the universe.

[jmt73]

Sorry, I just realised I skimmed the forums and posted in the wrong place.

[daddylonglegs]

Hello jmt73,

I moved this thread to a more appropriate forum (general)

Try the INDIRECT function....

[martindwilson]

heres an example using indirect

=VLOOKUP(A1,INDIRECT("'"&B1&"'!A1:B20"),2,FALSE)
sheet name goes in B1

[jmt73]

Thanks, I'll try again.
I have tried INDIRECT(), but must have been messing something up.

[jmt73]

As before, I still get a REF error.
Could it be the Sheet Name being on both sides of the colon?
ie 'First Budget'!$A$5:'First Budget'!$H$88
instead of
'First Budget'!$A$5:$H$88

[daddylonglegs]

It's not necessary to repeat the sheet name but it should still work if you do. What formula are you using?

[martindwilson]

also look carefully at the" and '
=VLOOKUP(A1,INDIRECT(" ' " &B1&" ' !A1:B20 ") ,2,FALSE)

[jmt73]

It's not necessary to repeat the sheet name but it should still work if you do. What formula are you using?

cell C1 is where I have entered "First Budget", which is the name of the corresponding worksheet.
ADDRESS(5,1,1,1,C1) returns 'First Budget'!$A$5
ADDRESS(88,8,1,1,C1) returns 'First Budget'!$H$88

I've been using TEXT() to combine them and give:
=TEXT(ADDRESS(5,1,1,1,TEXT(C1,"")),"")&":"&TEXT(ADDRESS(88,8,1,1,TEXT(C1,"")),"")
'First Budget'!$A$5:'First Budget'!$H$88

Then if I chuck it all inside INDIRECT(*,TRUE), I get a REF error.

Martin's suggestion works where I just hardwire the cell numbers - probably simpler too, which is a good indication...

[martindwilson]

i just hate indirect1
but
=VLOOKUP(B5,INDIRECT(SUBSTITUTE(CONCATENATE(ADDRESS(5,1,4,1,C1),":",ADDRESS(88,8,4,1,C1)),"'"&C1&"'!","",2)),2,FALSE)
works