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'!$...
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
- ok , see =SUM("'"&"C:\Users\james\OneDrive\Documents\Work\Financial\Sales Figures\"&indirect(CurrentYear)&"\[James.xls]Summary'!"$F$7:$F$18)
i think now it should work
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.
- HansVogelaar Thanks. I think the INDIRECT function needs a second parameter also though?
