Using Name Variable within External Workbook Lookup Path

Copper Contributor

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"))

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? 

@jamesbeale 

My apologies, I was too hasty. I have edited the formula in my previous reply.

@Hans Vogelaar Thanks. I think the INDIRECT function needs a second parameter also though?

@jamesbeale 

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.

@Hans Vogelaar 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.

@jamesbeale 

It should work:

 

S0206.png

 

S0207.png

 

@Hans Vogelaar I seem to have exactly the same as you but am getting a #REF! error, any ideas why?

 

jamesbeale_1-1615789763916.png

 

 

@jamesbeale 

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?

@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?

 

jamesbeale_0-1615797599834.png

jamesbeale_1-1615797667884.png

 

@Hans Vogelaar also I know the file path is 100% correct as if I remove the Year1 name and just put it in as a full file path, it works without an any error. So the error is only coming when inserting the Year1 variable.
only way is to use indirect as sum will not understand address c:\etc

@chahine still gives the same error even with INDIRECT used - see below

jamesbeale_1-1615800411916.png

 

@jamesbeale 

It looks OK to me. I'm afraid I'm out of ideas...

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