How do I use a variable in the reference to an external workbook

[Bimbette247]

My excel skills are very rusty and out of date. I have spent over three hours trying formulas I found with google but couldn't make anything work. Figured it was time to learn how to use a forum and ask for help.. This is my way of saying you're probably going to have to dumb down your answer for me.

I have a workbook for each week of the year, named WK xx 2018 Sales. Each workbook contains 7 sheets, one for each day of the week. I have constructed another workbook named WK 15 2018 Delivery. The delivery workbook rearranges information from WK 15 2018 Sales and also formats it. Next week I will make WK 16 2018 Sales from a "blank". I would also like to be able to construct WK 16 2018 delivery from a "blank".

This is the formula I'm using for the lookup in one cell:

='[wk 15 2018 sales.xlsx]MONDAY'!$F$22

The sheet reference "Monday" will not need to change. Neither will the cell address "$F$22). I just need to change the workbook reference from wk 15 to wk 16. I would prefer not to put extra information on any of the daily sheets as they are formatted so the driver can just print them out. I can easily add another sheet for data.

Is there an easy way to do this. I'd appreciate any help I can get.

BTW: What do you use Post Icons & Tags for?

 

[offthelip]

You need to use the Indirect function.

 

[Bimbette247]

You need to use the Indirect function.

Thanks for the reply. I have tried many many combinations of INDIRECT and CONCATENATE in the formula. Nothing has worked. Suspect failure of many was due to syntax errors.

Have given up on this approach. Had hoped to be able to get a very dumb approach to cloning the work sheets. Will just have to explain to co-users changing the spreadsheet the links are associated with in the Data/Connections tab.

 

[offthelip]

your problem is having spaces in your tab names: when using indirect you have to put them in specifically like this:

=INDIRECT("'[WK"&CHAR(32)&C1&CHAR(32)&"2018"&CHAR(32)&"Sales.xlsx]MONDAY'!$F$22")

I have assume the 15 and 16 are in C1

 

[Bimbette247]

Worked great.

Thanks for taking the time to reply. Had suspected it was because of the spaces in the file name but couldn't find any information on how to deal with it.