Forum Discussion

Copper Contributor

Mar 14, 2021

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'!$...

excel

Formulas and Functions

HansVogelaar

MVP

Mar 14, 2021

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

chahine

Iron Contributor

Mar 15, 2021

only way is to use indirect as sum will not understand address c:\etc

jamesbeale
Copper Contributor
Mar 15, 2021
chahine still gives the same error even with INDIRECT used - see below
- chahine
  Iron Contributor
  Mar 15, 2021
  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
- HansVogelaar
  MVP
  Mar 15, 2021
  jamesbeale
  
  It looks OK to me. I'm afraid I'm out of ideas...