Hi, would really appreciate some help with the formula below. As I understand it SUMPRODUCT, INDEX and MATCH should all work with a closed workbook but I still get #REF when I close the 2020 Performance Targets file. There are no tables present. Am I missing something? I'm using this to return a year to date figure, would happily consider an alternative!
=SUMPRODUCT(INDEX('[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!$D$1:$D$22,MATCH($D12,'[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!$C$1:$C$22,0)):INDEX('[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!$D$1:$P$22,MATCH($D12,'[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!$C$1:$C$22,0),$I$8))
=SUMPRODUCT(INDEX('[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!$D$1:$D$22,MATCH($D12,'[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!$C$1:$C$22,0)):INDEX('[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!$D$1:$P$22,MATCH($D12,'[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!$C$1:$C$22,0),$I$8))