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:
Thanks,
Mark.
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:
Code:
=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.