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'!$F$7:$F$18)
On a second worksheet called Settings, I have defined a cell named "CurrentYear" with the value "2020", which I want to replace the "2019" with in the current formula to save me from updating all of the years in the formulas manually, ie. =SUM('C:\Users\james\OneDrive\Documents\Work\Financial\Sales Figures\CurrentYear\[James.xls]Summary'!$F$7:$F$18)
Can anyone clarify the syntax to get this to work? I have tried inserting speechmarks and ampersands around the file path and name but then the external workbook lookup does not seem to work.
Thanks.
15 Replies
jamesbeale You can use the INDIRECT function, but (and this is a big BUT!), this function only works with references to another workbook if that workbook is open in Excel. Otherwise, INDIRECT will return #REF!
The formula would be
=SUM(INDIRECT("'C:\Users\james\OneDrive\Documents\Work\Financial\Sales Figures\" & CurrentYear & "\[James.xls]Summary'!$F$7:$F$18"))
- only way is to use indirect as sum will not understand address c:\etc
Hi HansVogelaar, thanks for your reply. I need it to work with the other workbook closed ideally. But where did you use the INDIRECT function? It was not there in the formula?
My apologies, I was too hasty. I have edited the formula in my previous reply.
