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'!$...
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?
The second argument to INDIRECT is optional. If you omit it, the first argument is treated as a reference in A1 style.
If you specify FALSE as second argument, the first argument is treated as a reference in R1C1 style.
Since your cell reference $F$7:$F$18 is in A1 style, there is no need to use the second argument.
- 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.