Forum Discussion

jamesbeale's avatar
jamesbeale
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
chahine's avatar
chahine
Iron Contributor
Mar 15, 2021
only way is to use indirect as sum will not understand address c:\etc
jamesbeale's avatar
jamesbeale
Copper Contributor
Mar 15, 2021

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

 

  • chahine's avatar
    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's avatar
    HansVogelaar
    MVP
    Mar 15, 2021

    jamesbeale 

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

Resources