I have the following statement-- =VLOOKUP(A4,Master!A56:E66,5,0) wich returns
a specific value.

We have some users that update the field under the master sheet and we then
summarise in a different sheet. If they copy this master sheet to a new sheet
and enter the new sheet name as Master1, then on the summary page I want to
automatatically change the sheet name to the new sheet name.

I was thinking of concatenate in a formula that could look like this

A1 data = Master1(Entered by user)
B2 data = a56:b66(Fixed range as the sheets are protected)

concatenate(a1,"!",b2) will return Master1!a56:b66 wich is the exact new
table array I want. How do I use it in Vlookup?

I was thinking Vlookup(a4,concatenate(a1,"!",b2),5,0), but this does not
work. Are there anything else I can use?

=Vlookup(a4,INDIRECT(a1&"!"&b2),5,0)

Does that help?

--
Regards,
Ron

If A1 had a worksheet name that required quotes (embedded spaces, say):

=Vlookup(a4,INDIRECT("'" & a1 & "'!"&b2),5,0)



Ron Coderre wrote:

=Vlookup(a4,INDIRECT(a1&"!"&b2),5,0)

Does that help?

--
Regards,
Ron

--

Dave Peterson

Thanks Ron, it works perfectly. Funny enough, I tried indirect but it didn't
work. Maybe I missed something somewhere.

Regards


Pieter

"Ron Coderre" wrote:

=Vlookup(a4,INDIRECT(a1&"!"&b2),5,0)

Does that help?

--
Regards,
Ron