Sum Data in a Range using unique dynamic references

[Jimmers]

Hi alll,

Just coming up with a difficult problem at the moment. Trying to SUM forecast data in a Range, but using unique variables to decide what that range is.

Forecast Sheet is as follows;

Demo Data.xlsx
	A
2	123456
Forecast Data

Stock Sheet is as follows;

Demo Data.xlsx
	F
25
Stock

We want to be able to see based on the Date stock goes overage the amount of forecast we have to sell on that SKU from the forecast table. I'm sure theres a way to do this, in my brain there will be a way to say that SKU 123344 goes short dated on 16/12/22, in that time period the total forecast is 1480, so we only have a risk of 20cs on that product. However it is falling out of my range of knowledge on how to Formulise this.

Any help would be appreciated.

 

[Peter_SSs]

Two things would help.

1.
Update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

2.
With XL2BB, select the range that you want to show before clicking Mini Sheet.

 

[Jimmers]

Hi,

Just realised XL2BB had failed.

Update as below;

Forecast;

Book1
	A	B	C	D
1	SKU	Week	Week Commencing	Forecast
2	123456	45	30/10/2022	100
3	123456	46	06/11/2022	20
4	123456	47	13/11/2022	300
5	123456	48	20/11/2022	400
6	123456	49	27/11/2022	100
7	123456	50	04/12/2022	20
8	123456	51	11/12/2022	300
9	123456	52	18/12/2022	400
10	123456	53	25/12/2022	100
11	123456	1	01/01/2023	20
12	123456	2	08/01/2023	300
13	123456	3	15/01/2023	400
14	123456	4	22/01/2023	100
15	122334	45	30/10/2022	100
16	122334	46	06/11/2022	20
17	122334	47	13/11/2022	300
18	122334	48	20/11/2022	400
19	122334	49	27/11/2022	100
20	122334	50	04/12/2022	20
21	122334	51	11/12/2022	300
22	122334	52	18/12/2022	400
23	122334	53	25/12/2022	100
24	122334	1	01/01/2023	20
25	122334	2	08/01/2023	300
26	122334	3	15/01/2023	400
27	122334	4	22/01/2023	100
Forecast

Cell Formulas
Range	Formula
C3:C14,C16:C27	C3	=C2+7

Stock

Book1
	A	B	C	D
1	SKU	BBD	Wk Commencing Out of Date	Total
2	123456	04/04/2023	06/11/2022	1
3	123456	24/04/2023	27/11/2022	220
4	123456	01/05/2023	04/12/2022	356
5	122334	14/12/2022	05/06/2022	402
6	122334	31/12/2022	19/06/2022	778
7	122334	28/02/2023	13/01/2023	90
Stock

I have also updated my account.

 

[Peter_SSs]

I have also updated my account.

Thanks for doing that.

SKU 123344 goes short dated on 16/12/22,

Where do we find that information in the sample data?

in that time period

In what time period? A time period sounds like a range from one time to another to me.

the total forecast is 1480,

How is that calculated from the sample data?

so we only have a risk of 20cs on that product.

How is that calculated?

 

[Jimmers]

Hi Peter,

Thanks for the responses, unfortunately I think my original post and the sample data have changed so I will retype my query;

So column C in the stock data shows us the week commencing date of when the SKU will go short dated from our POV, i.e 147 days prior to the bb4 date.

So the requirement in this instance would be to look at the forecast data and sum the total of the forecast for that SKU up to that week commencing date, so for example;

Line three; SKU 123456 goes short dated w/c 27/11/22 so I would then want to total forecast for SKU 123456 up to that w/c date so would sum total rows 2-5 on Forecast sheet, in this example it would be 820cs, as we only have 220cs of that SKU for that date we would class that as a mitigated short dated risk.

The dataset is also coming from a Power Query, so if a formula is possible, how we would put that into Power Query would be great appreciated.