Concatenate in a formula to reference a different worksheet

I have a workbook with 32 sheets in it - A sheet for each day of the month, a Client list sheet and a totals sheets.

What i'm doing is on the "Client" sheet the list of client's and i Vlookup cells on the "day" sheets to give me daily figures.

So im using the following formula which works fine.

=IFERROR(VLOOKUP("Total for account 76000/100830 on 76000/COMMHORI",'1st'!$H$1:$AF$1000,25,FALSE),"")

The problem i have is have to change the sheet ref for every client of every day of the month (in this formula it is "1st" and it obviously will need to change to"2nd" etc).

What i was was hoping to do was to concatenate the formula using a cell reference instead of the actual sheet name and then i could copy and paste it across.

Any help would be most welcome!

You can do that using the INDIRECT function, where you would list your sheet names in one column and the VLOOKUP/INDIRECT formula will pick up the appropriate sheet name. Attach a sample workbook so we can see how your data is laid out.

Hope this helps.

Pete

try

=IFERROR(VLOOKUP("Total for account 76000/100830 on 76000/COMMHORI",INDIRECT("'" &A2 &"'!$H$1:$AF$1000"),25,FALSE),"")

A2=Sheet name

help.xlsx


I have taken some data off but you should get the gist

Cheers

Thanks for all your replies.

You can use this in B2 of the Totals sheet:

=IFERROR(VLOOKUP("Total*",INDIRECT("'"&A2&"'!H:AF"),25,0),"")

then copy down. Note the asterisk in the lookup value - it is looking for the first entry in column H that begins with the word "Total".

Hope this helps.

Pete

Pete your 1 step ahead of me.
I've realised that where i have used "Total for account 76000/100830" and you have used "Total*"
what i really should do is have "Total for the Account" and then concatenate a cell reference to replace the account number and i can then copy down.
I assume it would look something like this
=IFERROR(VLOOKUP("Total for account"CONCATENATE,A2,INDIRECT("'"&$C$1&"'!H:AF"),25,0),"") does that look like im on the right road?

=IFERROR(VLOOKUP("Total for account " & A2,INDIRECT("'" & $C$1 &"'!H1:AF1000"),25,FALSE),"")

Thanks John.
For some reason im getting a blank cell. when i try and use your formula.
I looks like it should work .

What cell is blank? With name in A2 (say "ABCDEF") you should get

=IFERROR(VLOOKUP("Total for account ABCDEF", INDIRECT("'" & $C$1 &"'!H1:AF1000"),25,FALSE),"")

The return cell C3 is blank.
I was expecting the result you put down.
I have this in cell C2 which is using the first cell with "Total for account" =IFERROR(VLOOKUP("Total for account*",INDIRECT("'"&$C$1&"'!H:AF"),25,0),"") (which was what pete provided)
I have this in cell C3 and it works fine =IFERROR(VLOOKUP("Total for account 76000/110483 on 76000/COMMHORI",INDIRECT("'" &$C$1 &"'!$H$1:$AF$1000"),25,FALSE),"")
When i try the formula =IFERROR(VLOOKUP("Total for account " & A2,INDIRECT("'" & $C$1 &"'!H1:AF1000"),25,FALSE),"") im getting a blank.
Obviously im doing something wrong

Post your file (or sample of) with the "error".

for C3 then

cell A2=76000/110483 on 76000/COMMHORI

I did try it but i want to all the client for each day on a single sheet.
The account number is in cell A2 but it sits within a line of text on day sheet in H but i want the which has Total for account as it is also contained in the H but with different definition and that produces a different result.

John that work. But you what im going to say now!
really i only want the account number in that cell.
Any idea?

You will need to post a file so we can see how your data is organised.

I have posted a sample earlier the file was called help.xls

Yes, but that file had the sheet name in column A of the Totals sheet - everyone since Post#8 seems to have assumed that the sheet name is in column C, so I'm confused. Where does the account number appear? I would suggest putting the account numbers across row 1 starting with B1, then you can produce a table using this formula in B2:

=IFERROR(VLOOKUP("Total*"&B$1&"*",INDIRECT("'"&$A2&"'!H:AF"),25,0),"")

then you can copy this across and down, as required.

Hope this helps.

Pete

Good Morning Sorry if i have been giving conflicting info.

Just explain exactly what i would like to achieve.

I receive a daily sheet which i want to copy and paste into the corresponding day of the month ie data for the 15th will be pasted into sheet 15.

On the "Client" sheet in A will be posted an account number format "76000/xxxx" and their name is in be B.

So what i want to do is pull the daily commission number for each client on the Client sheet. So the figure for client AAA for the 3rd day of the month will be posted E2
and then f2 would contain the figure for the 4th day.

If you look on the daily data posted on say sheet 1 the daily total commission is in AF The problem is that column AF contains 2 versions so to make sure
i get the right 1 i was looking up cell in H and the correct data is "Total for the account 76000/xxxx. (xxxx being the data that changes for each client.

I hope this clears it up and once again thanks in advance for any help!

Put this in C2 of the clients sheet:

=IFERROR(VLOOKUP("Total for account*"&$A2&"*",INDIRECT("'"&C$1&"'!H:AF"),25,0),"")

then copy across and down as required.

Hope this helps.

Pete

Pete thanks very much that sorted it right out!