SUMPRODUCT INDEX MATCH not working with closed workbook

[Gem1979]

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

 

[jasonb75]

As far as I'm aware, your formula should work with the other workbook closed. There are some functions that don't work with closed workbooks, but none that you're using. That said, if any precedent ranges contain functions which do not work with closed workbooks then that could possibly be the cause.

When you close the workbook, are the references in the formula changing to show the full path instead of just the workbook name?

What happens if you try a simple formula like below, does that still error when you close the file?

='[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!$D$1

 

[RoryA]

I don't think that the syntax using INDEX(...) on either side of : to construct a range works with closed workbooks. Perhaps because of volatility.

 

[Gem1979]

As far as I'm aware, your formula should work with the other workbook closed. There are some functions that don't work with closed workbooks, but none that you're using. That said, if any precedent ranges contain functions which do not work with closed workbooks then that could possibly be the cause.

When you close the workbook, are the references in the formula changing to show the full path instead of just the workbook name?

What happens if you try a simple formula like below, does that still error when you close the file?

='[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!$D$1

Thank you Jasonb75, no the simple formula doesn't error when the file is closed. Might be as RoryA says, the range is causing an issue?

 

[Gem1979]

I don't think that the syntax using INDEX(...) on either side of : to construct a range works with closed workbooks. Perhaps because of volatility.

Thank you RoryA, that could well be the issue.

 

[Gem1979]

I don't think that the syntax using INDEX(...) on either side of : to construct a range works with closed workbooks. Perhaps because of volatility.

The formula works if I take out the range but obviously don't get the result I need;
=IFERROR(IFERROR(SUMPRODUCT(INDEX('MB ex DSP'!$D$1:$D$262,MATCH($D13,'MB ex DSP'!$C$1:$C$262,0)),$I$8),SUMPRODUCT(INDEX('MB ex DSP'!$D$1:$D$262,MATCH($D13,'MB ex DSP'!$B$1:$B$262,0)),$I$8)),"")

Any thoughts on an alternative? I might give in and just have YTD sums on the closed workbook so I can have a simple index match but I don't want to be beaten ?

 

[jasonb75]

Well spotted, Rory! I had looked at the functions used but missed the union.

See if this works, think I have the parentheses in the right places.

=SUMPRODUCT(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),0)*(((COLUMN('[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!$D$1:$P$1)-COLUMN('[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!$D$1)+1)<=$I$8))

 

[Gem1979]

Well spotted, Rory! I had looked at the functions used but missed the union.

See if this works, think I have the parentheses in the right places.

=SUMPRODUCT(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),0)*(((COLUMN('[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!$D$1:$P$1)-COLUMN('[2020 Performance Targets Revised H2.xlsx]MB ex DSP'!$D$1)+1)<=$I$8))

Oo you star. it worked! Now I will try and understand it ? I've not used COLUMN before so thank you for showing me something new. Thank you for your time, much appreciated.