How to use a value from another cell as part of a cell reference

[Bentley_Wood]

I have a spreadsheet with weekly orders. Each tab is named 'WK (1)', 'WK (2)', 'WK (3)' etc.

I have a separate sheet which is pulling all the data from each week so it can be viewed on one page.

This is what I've used so far which works:

='K:\Production\[Order Sheet 2020.xlsm]WK (1)'!$O5

I have the week numbers in row 1 and want to be able to change the tab reference using a formula for example:

='K:\Production\[Order Sheet 2020.xlsm]WK (A1)'!$O5

Is there a way to get this to work?

 

[Fluff]

You can use the Indirect function, but the order sheet will need to be open.

 

[Bentley_Wood]

You can use the Indirect function, but the order sheet will need to be open.

Thanks, am I on the right lines with this? I keep getting a #REF error.

=INDIRECT("K:\Production\[Order Sheet 2020.xlsm]"&A1&"!$O5")

 

[Fluff]

Close, you need to keep the ' around the workbook name & sheet

=INDIRECT("'K:\Production\[Order Sheet 2020.xlsm]"&A1&"'!$O5")

where A1 needs to have the whole sheet name, or

=INDIRECT("'K:\Production\[Order Sheet 2020.xlsm]WK ("&A1&")'!$O5")

if you just want the week number

 

[Bentley_Wood]

Close, you need to keep the ' around the workbook name & sheet

=INDIRECT("'K:\Production\[Order Sheet 2020.xlsm]"&A1&"'!$O5")

where A1 needs to have the whole sheet name, or

=INDIRECT("'K:\Production\[Order Sheet 2020.xlsm]WK ("&A1&")'!$O5")

if you just want the week number

That's worked now, Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Bentley_Wood]

You're welcome & thanks for the feedback.

Is there a way to get $O5 to count up when dragging the formula down? INDIRECT seems to have stopped this as it worked on the original formula.

 

[Fluff]

How about

=INDIRECT("'K:\Production\[Order Sheet 2020.xlsm]WK ("&$A$1&")'!$O"&ROW())

 

[Bentley_Wood]

How about

=INDIRECT("'K:\Production\[Order Sheet 2020.xlsm]WK ("&$A$1&")'!$O"&ROW())

Perfect, thank you!

 

[Fluff]

My pleasure.