Forum Discussion
Using Name Variable within External Workbook Lookup Path
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"))
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.