SUM,INDEX produces #REF when target file is not open

[santaklas]

Hi everyone,

First post for me on this great forum.

I have the following challenge on my hands:

I have a very large table of data (about 24500 rows and at least 52 columns) of weekly selling for products that are outlined as below (very simplified). There is always the same products for every store, and a certain number of stores are grouped into regions.

	A	B	C	D	E	F	G
1	formula:	Region	Store #	Product	week 1	week 2	week 3
2	=C1&D1	1	1	Hats	45	55	52
3	=C2&D2	1	1	Boots	102	95	115
4	etc.	1	1	Bags	51	85	65
5		1	2	Hats	150	125	200
6		1	2	Boots	150	250	300
7		1	2	Bags	48	55	65
8		1	3	Hats	14	26	51
9		1	3	Boots	115	120	160
10		1	3	Bags	110	55	85

<tbody>
</tbody>

Now what I am trying to do is from another simple sheet enter store#, start week, and end week to get an accumulated figure of each products selling, like this:

	A	B	C
1		STORE #:	1
2		Start Week:	week 2
3		End Week:	week 3
4
5	=C1&B5	Hats:	107
6	=C1&B6	Boots:	210
7	=C1&B7	Bags:	150

<tbody>
</tbody>

To be able to find the right row in the big data file (called Weekly MASTER.xlsx, tab called Data below) I have added a hidden row (row A) in both sheets that just adds the store number to the product name (i.e. 1Hats, 1Boots, 1Bags, etc.) to be able to use MATCH. I have then used INDEX to find the starting point and end point of my SUM.

In words I wanted the formula to find the correct row depending on store number and product name, and then sum between the chose starting point and end point (the weeks).

The formula I have been using in cell C5 (Hats SUM) above is looking like this:

=SUM(INDEX('[Weekly MASTER.xlsx]Data'!E2:G10,MATCH(A5,'[Weekly MASTER.xlsx]Data'!A:A,0),MATCH(C2,'[Weekly MASTER.xlsx]Data'!E1:G1,0))
:INDEX('[Weekly MASTER.xlsx]Data'!E2:G10,MATCH(A5,'[Weekly MASTER.xlsx]Data'!A:A,0),MATCH(C3,'[Weekly MASTER.xlsx]Data'!E1:G1,0)))

Now, this formula works great as long as 'Weekly MASTER.xlsx' is open, but when it is closed it produces #REF in cell C5.

I am no excel master so in terms of advance formulas this is as much as I know, meaning I am sure there are better ways to do this.

If anyone can help me make sure this works when the target file is closed it would be very appreciated.

Thank you.

Kind regards
Klas

 

[XOR LX]

Hi.

I notice that your range is being determined from just three columns, i.e. Data'!E1:G1.

So you're only ever summing from a choice of 3 possibilities, i.e. either column E to column F, column F to column G, or column E to column G? That's it?

Regards

 

[santaklas]

Hi.

I notice that your range is being determined from just three columns, i.e. Data'!E1:G1.

So you're only ever summing from a choice of 3 possibilities, i.e. either column E to column F, column F to column G, or column E to column G? That's it?

Regards

Hi,

No sorry that is just the very simplified version for the sake of being easy to show in this thread. The real table (and corresponding MATCH formulas) is about 24500 rows and up to 75 differenct columns. So it is a massive number of possible SUMs available.

Regards

 

[santaklas]

Hi.

I notice that your range is being determined from just three columns, i.e. Data'!E1:G1.

So you're only ever summing from a choice of 3 possibilities, i.e. either column E to column F, column F to column G, or column E to column G? That's it?

Regards

Hi again,

To be clear, the exact formula looks like this:


=IFERROR(SUM(INDEX('[Weekly MASTER.xlsx]Data'!$F$1:$FF$24573,MATCH(B19,'[Weekly MASTER.xlsx]Data'!$C:$C,0),MATCH($A$16,'[Weekly MASTER.xlsx]Data'!$F$3:$FF$3,0)):INDEX('[Weekly MASTER.xlsx]Data'!$F$1:$FF$24573,MATCH(B19,'[Weekly MASTER.xlsx]Data'!$C:$C,0),MATCH($A$17,'[Weekly MASTER.xlsx]Data'!$F$3:$FF$3,0))),"")

Sorry for the confusion.

KR

 

[XOR LX]

Thanks.

Your current formula would also suggest that the values to be summed do not come from the same row as that which contains the value in A5, but rather from that immediately below this one.

Can you confirm?

Regards

 

[XOR LX]

Hi again,

To be clear, the exact formula looks like this:


=IFERROR(SUM(INDEX('[Weekly MASTER.xlsx]Data'!$F$1:$FF$24573,MATCH(B19,'[Weekly MASTER.xlsx]Data'!$C:$C,0),MATCH($A$16,'[Weekly MASTER.xlsx]Data'!$F$3:$FF$3,0)):INDEX('[Weekly MASTER.xlsx]Data'!$F$1:$FF$24573,MATCH(B19,'[Weekly MASTER.xlsx]Data'!$C:$C,0),MATCH($A$17,'[Weekly MASTER.xlsx]Data'!$F$3:$FF$3,0))),"")

Sorry for the confusion.

KR

Ah, that's a significant difference. The starting row for the range being passed to the initial INDEX clause is a 1 here; in your previous example it was a 2!

Getting a bit confusing!

Regards

 

[santaklas]

Ah, that's a significant difference. The starting row for the range being passed to the initial INDEX clause is a 1 here; in your previous example it was a 2!

Getting a bit confusing!

Regards

Sorry about that! I wanted to simplify the references a bit but I might have made it more confusing.

So the table that I am summing from looks more or less like this below, with store number attached to product. There are more than 3 products but that's not the point.

	A	B	C	D	E	...	BR	BS
1		week 1	week 2	week 3	week 4	...	week 51	week 52
2	1Hats
3	1Boots
4	1Bags
5	2Hats
6	2Boots
7	2Bags
...	...
24500	850Hats
	850Boots
	850Bags

<tbody>
</tbody>

The question is really how to find and sum part of row (say row "235Bags" between "Week 15" and "Week 42") based on input in another file, with the data file being closed.


Please let me know if I need to clarify anything else

Kind regards

 

[XOR LX]

Based on the formula you gave in post #1, something like:

=SUMPRODUCT((COLUMN('[Weekly MASTER.xlsx]Data'!E1:G1)>=MATCH(C2,'[Weekly MASTER.xlsx]Data'!1:1,0))*(COLUMN('[Weekly MASTER.xlsx]Data'!E1:G1)<=MATCH(C3,'[Weekly MASTER.xlsx]Data'!1:1,0))*(INDEX('[Weekly MASTER.xlsx]Data'!E1:G10,MATCH(A5,'[Weekly MASTER.xlsx]Data'!A1:A10,0),)))

which will work even when Weekly MASTER is closed

See if you can adapt that to your actual workbook. Note that the part:

Data'!1:1

represents the entire first row within that sheet.

N.B. Very interesting - I never knew that this syntax with INDEX:INDEX was not valid for closed workbooks.

Regards

 

[santaklas]

Based on the formula you gave in post #1, something like:

=SUMPRODUCT((COLUMN('[Weekly MASTER.xlsx]Data'!E1:G1)>=MATCH(C2,'[Weekly MASTER.xlsx]Data'!1:1,0))*(COLUMN('[Weekly MASTER.xlsx]Data'!E1:G1)<=MATCH(C3,'[Weekly MASTER.xlsx]Data'!1:1,0))*(INDEX('[Weekly MASTER.xlsx]Data'!E1:G10,MATCH(A5,'[Weekly MASTER.xlsx]Data'!A1:A10,0),)))

which will work even when Weekly MASTER is closed

See if you can adapt that to your actual workbook. Note that the part:

Data'!1:1

represents the entire first row within that sheet.

N.B. Very interesting - I never knew that this syntax with INDEX:INDEX was not valid for closed workbooks.

Regards

Hi,

That works great, thank you so much for your help! While I am not 100% sure what the formula does I'm very happy to use it

If you have the time could you enlighten me on what this does exactly?

Thanks again!

KR

 

[RoryA]

N.B. Very interesting - I never knew that this syntax with INDEX:INDEX was not valid for closed workbooks.

It works for me in both 2010 and 2016 preview.