Mar 14 2021 03:33 AM - edited Mar 14 2021 03:34 AM
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.
Mar 14 2021 04:46 AM - edited Mar 14 2021 05:08 AM
@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"))
Mar 14 2021 05:00 AM
Hi @Hans Vogelaar, 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?
Mar 14 2021 05:11 AM
My apologies, I was too hasty. I have edited the formula in my previous reply.
Mar 14 2021 05:21 AM
Mar 14 2021 05:43 AM
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.
Mar 14 2021 06:00 AM
Mar 14 2021 06:11 AM
Mar 14 2021 11:30 PM
Mar 15 2021 01:17 AM
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?
Mar 15 2021 01:43 AM
@Hans Vogelaar 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?
Mar 15 2021 01:52 AM
Mar 15 2021 02:22 AM
Mar 15 2021 02:28 AM
Mar 15 2021 03:44 AM
It looks OK to me. I'm afraid I'm out of ideas...
Mar 15 2021 04:41 AM