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]

Sure.

The parts:

MATCH(C2,'[Weekly MASTER.xlsx]Data'!1:1,0)

and:

MATCH(C3,'[Weekly MASTER.xlsx]Data'!1:1,0)

find the relative position of the first occurrence of the values in C2 and C3 respectively within the first row of the Data tab of the Weekly MASTER workbook.

Let's assume that:

MATCH(C2,'[Weekly MASTER.xlsx]Data'!1:1,0)

results in 6, and that:

MATCH(C3,'[Weekly MASTER.xlsx]Data'!1:1,0)

results in 7.

(This would tell us that the values in C2 and C3 occur in column F and column G respectively of the Data tab.)

We then ask the formula to determine which of the columns between column E and column G inclusive fall within the boundaries of these two values, so that the part:

(COLUMN('[Weekly MASTER.xlsx]Data'!E1:G1)>=MATCH(C2,'[Weekly MASTER.xlsx]Data'!1:1,0))

which is, in this example:

(COLUMN('[Weekly MASTER.xlsx]Data'!E1:G1)>=6)

is equivalent to:

({5,6,7}>=6)

(The COLUMN function simply returning the index of each the columns within the range E1:G1.)

And this is:

{FALSE,TRUE,TRUE}

We perform a similar test as to which of the columns fall below the upper boundary, so that:

(COLUMN('[Weekly MASTER.xlsx]Data'!E1:G1)<=MATCH(C3,'[Weekly MASTER.xlsx]Data'!1:1,0))

which here is:

(COLUMN('[Weekly MASTER.xlsx]Data'!E1:G1)<=7)

i.e.:

({5,6,7}<=7)

which is:

{TRUE,TRUE,TRUE}

When we multiply the results of these two arrays (which is precisely what SUMPRODUCT does), only the cases corresponding to a TRUE in each of these arrays will be considered, since:

{FALSE,TRUE,TRUE}*{TRUE,TRUE,TRUE}

is:

{0,1,1}

and so we know that we will only be summing from columns F and G, not E.

We now need to make sure that we pass the correct row to be summed, which is determined by:

(INDEX('[Weekly MASTER.xlsx]Data'!E1:G10,MATCH(A5,'[Weekly MASTER.xlsx]Data'!A1:A10,0),))

Let's assume that the first occurrence of the value in A5 occurs in cell A9 of the Data tab, so that the MATCH part above would return 9, and so we would have:

(INDEX('[Weekly MASTER.xlsx]Data'!E1:G10,9,))

When a row (or column) parameter is zero (or, equivalently, omitted) in INDEX, Excel returns the entire contents of the column (or row) being referenced. Effectively, the above is telling Excel to return the whole of row 9 contained within the the range E1:G10, i.e. E9:G9.

Hence, the above becomes (inserting some random values):

{12,13,14}

We can now put all the pieces back into the original formula, so that:

=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 is now:

=SUMPRODUCT({FALSE,TRUE,TRUE}*{TRUE,TRUE,TRUE}*{12,13,14})

gives:

=SUMPRODUCT({0,1,1}*{12,13,14})

i.e.:

=SUMPRODUCT({0,13,14})

i.e.:

27.

Hope that helps.

Regards

 

[XOR LX]

It works for me in both 2010 and 2016 preview.

After recommitting the formula as well (via going into the formula bar)?

Regards

 

[RoryA]

Yup. Not a speedy recalc but it works.

 

[XOR LX]

Yup. Not a speedy recalc but it works.

Hmmm. I'm using 2010 and I get a #REF! error.

Regards

 

[XOR LX]

It would seem that the full filepath string in the second reference makes this syntax invalid, e.g.:

=SUM('C:\Documents and Settings\Desktop\[Weekly MASTER.xlsx]Data'!E1:'C:\Documents and Settings\Desktop\[Weekly MASTER.xlsx]Data'!G1)

and my machine can't cope with this not being simply:

=SUM('C:\Documents and Settings\Desktop\[Weekly MASTER.xlsx]Data'!E1:G1)

Regards

 

[RoryA]

I am using this exact formula:

=SUM(INDEX('C:\Users\Rory\Documents\Forums\MrExcel\[Weekly MASTER.xlsx]Data'!$E$1:$FF$25000,MATCH(A5,'C:\Users\Rory\Documents\Forums\MrExcel\[Weekly MASTER.xlsx]Data'!$A:$A,0),MATCH($C$2,'C:\Users\Rory\Documents\Forums\MrExcel\[Weekly MASTER.xlsx]Data'!$E$1:$FF$1,0))
:INDEX('C:\Users\Rory\Documents\Forums\MrExcel\[Weekly MASTER.xlsx]Data'!$E$1:$FF$25000,MATCH(A5,'C:\Users\Rory\Documents\Forums\MrExcel\[Weekly MASTER.xlsx]Data'!$A:$A,0),MATCH($C$3,'C:\Users\Rory\Documents\Forums\MrExcel\[Weekly MASTER.xlsx]Data'!$E$1:$FF$1,0)))


Excel build: 14.0.7145.5000 32 bit.

I should note that I do have a recollection of this sort of formula failing before but can't recall the specifics offhand.

 

[XOR LX]

I'm on version 14.0.7128.5000 (32-bit).

Let me know if it comes back to you!

Cheers

 

[RoryA]

It may take me a while to locate the post I'm thinking of as I can't even remember which forum it was...