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?