Forum Discussion
Using Name Variable within External Workbook Lookup Path
Hi all, I have a lookup to an eternal workbook currently taking place using the following formula: =SUM('C:\Users\james\OneDrive\Documents\Work\Financial\Sales Figures\2019\[James.xls]Summary'!$...
HansVogelaar thanks. I have entered the formula as you said above and with the second workbook open, but still get a #REF! error. Any ideas why?
Below is my formula:
=SUM(INDIRECT("'C:\Users\james\OneDrive\Documents\Work\Financial\Sales Figures\" & Year1 & "\[James.xls]Summary'!$D$7:$D$18"))
where the value of the cell referenced by Year1 is 2021.
Below is my formula:
=SUM(INDIRECT("'C:\Users\james\OneDrive\Documents\Work\Financial\Sales Figures\" & Year1 & "\[James.xls]Summary'!$D$7:$D$18"))
where the value of the cell referenced by Year1 is 2021.
- HansVogelaar also I know the file path is 100% correct as if I remove the Year1 name and just put it in as a full file path, it works without an any error. So the error is only coming when inserting the Year1 variable.
HansVogelaar recalculate doesn't do anything and file path is correct - see below if I evaluate the formula - file path is definitely correct and it seems to insert the year correctly into the path. Is there any formatting error in the file path I am missing?
If you opened Any Shearer.xls after creating the formula (or opening the workbook with the formula), you may have to recalculate the formulas (F9).
If not: are you absolutely sure that the path and filename are correct?