Ref data in another file

[jtodd]

Is this possible ?
I have a work book with sheets that are linked as the link below

=S:\RDC\RDC OUTBOUND\Shift Planner 2020-2021\Week 1\[planning tracker.xls]Sunday'!$D$3

i am trying to create a master sheet so when the user changes the week number the link changes ie
Have the formula refer to a week number typed in a cell , a1 = Week 1 , when the user changes a1 to Week 2 the link changes to Week 2.
Have tried Idirect and abslolute cell refs without success ,

Can anybody help or is it a lost cause

 

[John_w]

An INDIRECT formula should work as long as you specify the full folder path, workbook name, sheet name and cell reference:

Suppose the Master sheet contains "Week 1", without the quotes, in A1, then:

=INDIRECT("'S:\RDC\RDC OUTBOUND\Shift Planner 2020-2021\" & Master!A1 & "\[planning tracker.xls]Sunday'!$D$3")

The above formula will return a #REF error if you open planning tracker.xls in the "Week 2" folder and Master!A1 is not "Week 2". The formula should return the correct value after you change Master!A1 to same as the 'Week n' folder that you opened the workbook from. Use the IFERROR function to prevent the #REF error:

=IFERROR(INDIRECT("'S:\RDC\RDC OUTBOUND\Shift Planner 2020-2021\" & Master!A1 & "\[planning tracker.xls]Sunday'!$D$3"),"")