Excel formula to get sheet name from a cell

[love_excel1948]

I am trying to use a formula to reference a worksheet by getting the sheet name from a cell as shown below

=IF(A34="","",MAX(Client10!C$3:C$33))

I have about 50 sheets and want to sect the sheet depending on the row. I have tried to use CONCAT to build the sheetname but cannot get it to work in the formula.

All the sheets start with Client and column A contains a number which needs to be added to Client to give me the sheetname.

[#2]

Try this:
=IF(A34=“”,””,
MAX(INDIRECT(“Client”&A34&
“!C3:C33”)))

[#3]

@love_excel1948 

That could be like 

MAX(INDIRECT("Client" & A1 & "!C$3:C$33"))

for the second part of the formula.

[#4]

Many thanks for this. It has worked a treat.

[#5]

Many thanks this has worked.

[#6]

You’re welcome.