Help with proof of concept -

[mick0005]

Hey all –

I have a spreadsheet that I receive weekly with updated data from another department. It basically provides forecasted quantities at the daily level for multiple products across multiple locations. What I would like to do is come up with a way for users to select a range of dates they want forecasted quantities for as well as for which locations and which products, and they would receive output of the aggregated quantities for that date range, subtotaled first by product then location (location we are concerned about being the Adcoop). I have provided a small sample of the type of data I’d be using to do this. I have also shown an output I manually generated of what I’d like it to look like.

I don’t know if a macro is the best way to do this, or a formula, or something else… I am open to ideas on the vision… then I will see if someone can help with the execution.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Product	Division	Region	Adcoop	DC	Rest_Count	Units_09_26_2011	Units_09_27_2011	Units_09_28_2011	Units_09_29_2011	Units_09_30_2011
2	Wrench	CENTRAL DIVISION-0150000000	CHICAGO	CHICAGO IL CP-0007	96	29	9,269	9,475	9,795	10,102	11,384
3	Wrench	CENTRAL DIVISION-0150000000	CHICAGO	CHICAGO IL CP-0007	99	458	191,163	194,461	201,463	206,704	226,528
4	Wrench	CENTRAL DIVISION-0150000000	CHICAGO	CTRL IL IL CP-0016	47	52	18,373	18,910	19,317	20,074	22,516
5	Wrench	CENTRAL DIVISION-0150000000	CHICAGO	ILIAMO MO CP-0011	47	2	356	359	357	390	460
6	Wrench	CENTRAL DIVISION-0150000000	NEW YORK	MLWAUKE WI CP-0053	96	123	38,896	40,345	41,672	42,938	47,125
7	Wrench	CENTRAL DIVISION-0150000000	NEW YORK	PEORIA IL CP-0013	47	38	13,254	13,610	13,987	14,590	16,386
8	Hammer	CENTRAL DIVISION-0150000000	NEW YORK	QUAD CITIS CP-0044	99	2	523	539	554	588	687
9	Hammer	CENTRAL DIVISION-0150000000	NEW YORK	ROCKFRD IL CP-0056	96	31	11,294	11,525	12,006	12,502	14,071
10	Hammer	CENTRAL DIVISION-0150000000	NEW YORK	ST LOU MO CP-0014	47	2	938	919	964	1,038	1,233
11	Hammer	CENTRAL DIVISION-0150000000	CHICAGO	BATON ROUGE CP-0131	20	6	1,676	1,739	1,786	1,854	2,118
12	Hammer	CENTRAL DIVISION-0150000000	SOUTHERN	BATON ROUGE CP-0131	67	47	14,889	15,052	15,717	16,211	18,249
13	Hammer	CENTRAL DIVISION-0150000000	SOUTHERN	BILOXI-GLFP CP-0125	20	26	6,688	6,838	6,987	7,274	8,090
14	Screwdriver	CENTRAL DIVISION-0150000000	GREAT BARRIER	CEN ARK AK CP-0098	37	79	25,345	25,844	26,144	27,830	31,333
15	Screwdriver	CENTRAL DIVISION-0150000000	GREAT BARRIER	CNTR LOUISI CP-0133	20	12	3,804	3,827	3,939	4,140	4,787
16	Screwdriver	CENTRAL DIVISION-0150000000	SOUTHERN	GRW-GRV MS CP-0158	20	1	278	284	273	304	402
17	Screwdriver	CENTRAL DIVISION-0150000000	GREAT BARRIER	GRW-GRV MS CP-0158	37	3	1,010	982	1,028	1,111	1,377
18	Screwdriver	CENTRAL DIVISION-0150000000	GREAT BARRIER	GRW-GRV MS CP-0158	60	10	3,228	3,134	3,263	3,502	4,391
19	Nails	CENTRAL DIVISION-0150000000	GREAT BARRIER	JACKSON MS CP-0123	20	44	14,537	14,600	14,959	15,746	18,578
20	Nails	CENTRAL DIVISION-0150000000	MICHIGAN	LAFAYETTE L CP-0129	20	30	8,667	8,757	9,099	9,507	10,993
21	Nails	CENTRAL DIVISION-0150000000	MICHIGAN	LAKE CHARLE CP-0132	20	13	4,128	4,175	4,313	4,498	5,018
22	Nails	CENTRAL DIVISION-0150000000	MICHIGAN	LRL-HAT MS CP-0161	20	13	4,351	4,401	4,447	4,780	5,309
23	Nails	CENTRAL DIVISION-0150000000	MICHIGAN	MEMPHIS TN CP-0065	37	2	294	290	296	317	373
Sheet1

So here is the output I’d like… in this example the selection criteria I would choose would be:

Product: Hammer
Adcoop: Baton Rouge and Biloxi
Date range: 9/27 – 9/29

Excel Workbook
	A	B	C	D	E	F	G	H
1	Product	Division	Region	Adcoop	DC	Rest_Count	9/27 - 9/29 TOTAL
2	Hammer	CENTRAL DIVISION-0150000000	CHICAGO	BATON ROUGE CP-0131	20	6	5,379
3	Hammer	CENTRAL DIVISION-0150000000	SOUTHERN	BATON ROUGE CP-0131	67	47	46,980
4						TOTAL	52,359
5	Hammer	CENTRAL DIVISION-0150000000	SOUTHERN	BILOXI-GLFP CP-0125	20	26	6,838
6						TOTAL	6,838
7
Sheet2

 

[rdw72777]

This one sounds like fun. Curious what your skill level is with pivot tables. Your concept might actually be easier than you think, but I need to brainstorm.

 

[mick0005]

This one sounds like fun. Curious what your skill level is with pivot tables. Your concept might actually be easier than you think, but I need to brainstorm.

I like where you went with that one cuz Pivot Tables was my first thought as well... only issue with that is the daily forecasts go out up to 6 months... users would need to select on average at least 30-90 days of forecast typically. For my example I made it only a few days to make it easy but that's not exactly typical. With a pivot table you'd need to drag those fields down into a row or column for every single day you want a forecast for and I don't think it will aggregate/sum across columns...

My pivot table skill level is pretty good... better than anyone I know at my office, but probably only intermediate compared to many of the users on this board.

 

[rdw72777]

Hmm, well that is an onion in the ointment. Is this a file you get brand new each cycle or do columns get deleted and re-added for new dates.

In other words, could you maintain formulas in columns of to the right (i.e. after all of your forecast data) or would you need to re-create them in a new version of the file every time a new version is created? Also, would you be allowed to insert a row above your headers (that would be the best way to format your headers to get just the date value)?

Also, we could always create macro to create a pivot table to do a pivot on everything, and then pass through your user variables through there and thus the people wouldn't need to select the dates, the macro could do it.

Let me know your thoughts. I'll send you a private message with my email, if you want to send me the structure of the file (without proprietary data) so i could get a better feel, let me know.

 

[mick0005]

Hmm, well that is an onion in the ointment. Is this a file you get brand new each cycle or do columns get deleted and re-added for new dates.

In other words, could you maintain formulas in columns of to the right (i.e. after all of your forecast data) or would you need to re-create them in a new version of the file every time a new version is created? Also, would you be allowed to insert a row above your headers (that would be the best way to format your headers to get just the date value)?

Also, we could always create macro to create a pivot table to do a pivot on everything, and then pass through your user variables through there and thus the people wouldn't need to select the dates, the macro could do it.

Let me know your thoughts. I'll send you a private message with my email, if you want to send me the structure of the file (without proprietary data) so i could get a better feel, let me know.

Just shot you an email buddy -

The file will be renewed each week with refreshed dates that replace the old dates (delete the prior week's days and add the new days... it is a rolling 6 months) The columns will always remain the same.