SUMPRODUCT INDEX MATCH not working with closed workbook

Gem1979

New Member
Joined
Aug 28, 2014
Messages
19
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))
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,981
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Aug 28, 2014
Messages
19
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

New Member
Joined
Aug 28, 2014
Messages
19

ADVERTISEMENT

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

New Member
Joined
Aug 28, 2014
Messages
19
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-known Member
Joined
Dec 30, 2008
Messages
11,981
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 28, 2014
Messages
19
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,336
Messages
5,601,023
Members
414,421
Latest member
tonybear1994

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top