Referencing another workbook, where the name changes...INDIRECT?

[markw996]

Hi,

I have 52 workbooks (one for each week) and I need to reference cell G73 in the workbook from the previous week.

I have created the following formula which displays the path, filename, tab name, and cell.

The problem is that the formula displays all of this correct information in a text string format, using INDIRECT simply returns "#Ref" even though the other workbook exists in the correct location.

Do I need to use multiple INDIRECT statements? Or am I missing a much simpler way of performing the action?

FORMULA:

=INDIRECT("='" & LEFT(CELL("filename"),SEARCH("Wk",CELL("filename"))+1) & MID(CELL("filename"),SEARCH("Wk",CELL("filename"))+2, SEARCH(".xls",CELL("filename"))-SEARCH("Wk",CELL("filename"))-2)-1 & RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH(".",CELL("filename"))+1) & "'!$G$73")

Thanks,
Mark.

 

[VoG]

INDIRECT will not work with closed workbooks.

You could try Harlan Grove's PULL function Excel “Pull” Function: Creating dynamic links to closed workbooks | Numbermonger

 

[Teeroy]

Indirect.ext from the Morefunc add-in will also allow referencing a closed workbook but if the file is large then you pay a time penalty for the ability to access the file while closed.