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.
<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:
<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
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