Making a week by week comparison chart

[bh24524]

Hi, so I've been asked by my boss to see about starting a chart of warehouse production by week in several different categories. He is the first one in any job I've ever had to ask me to use the chart feature in Excel so sadly, my chart knowledge is sorely lacking. I want to make a chart that will compare up to 7 categories week by week and all I can think to do is just copy and paste each week with a 1 row gap between each. The data I exported to Excel looks like this layout:

I pasted the one for current week right below last week's leaving a row gap in between them. So at this point, I am completely lost on what to do. I am guessing he wants to compare the Perf % numbers on each week in column B that go with column A's headers. Mind you, this will be an ongoing thing that I add to as we complete a week, so the chart will be updated. I just have no idea where to go from here once I paste the data. Is pasting the data one space below like I have the best way to do this or is there better? If I could get some guidance on this, it would be much appreciated!

 

[sijpie]

Hi bh24524,

It is perfectly OK to store the weeks as you do. The handiest thing is to make a separate table for the graph, which links to your week tables. Then you can simply graph this table. Use the suggested graph types and try several to see which look good. Then discuss with your boss which one he likes best.

For the screenshots below I have made such a table in the same sheet, but in reality it will be better to put the table in a separate sheet. You will need to adjust the formulas to include the sheet name in front of the "B"in the INDIRECT formula. Something like =INDIRECT("Sheet2!B"&U$2+$S4). You only need to write the formula in the top left cell, then you can copy down and to the right

		8​	22​	36​	50​	64​
	Week	17-nov​	24-nov​	1-dec​	8-dec​	15-dec​
0​	Let Dows	73​	70​	0​	0​
1​	Loading	102​	94​	0​	0​
2​	Order Selection	95​	92​	0​	0​
3​	Put Away	91​	69​	0​	0​
4​	Receiving	104​	81​	0​	0​
5​	Total	91​	85​	0​	0​

with formulas visible:

		8​	=U2+14	=V2+14	=W2+14	=X2+14
	Week	17-nov​	=U3+7	=V3+7	=W3+7	=X3+7
0​	Let Dows	=INDIRECT("B"&U$2+$S4)	=INDIRECT("B"&V$2+$S4)	=INDIRECT("B"&W$2+$S4)	=INDIRECT("B"&X$2+$S4)
1​	Loading	=INDIRECT("B"&U$2+$S5)	=INDIRECT("B"&V$2+$S5)	=INDIRECT("B"&W$2+$S5)	=INDIRECT("B"&X$2+$S5)
2​	Order Selection	=INDIRECT("B"&U$2+$S6)	=INDIRECT("B"&V$2+$S6)	=INDIRECT("B"&W$2+$S6)	=INDIRECT("B"&X$2+$S6)
3​	Put Away	=INDIRECT("B"&U$2+$S7)	=INDIRECT("B"&V$2+$S7)	=INDIRECT("B"&W$2+$S7)	=INDIRECT("B"&X$2+$S7)
4​	Receiving	=INDIRECT("B"&U$2+$S8)	=INDIRECT("B"&V$2+$S8)	=INDIRECT("B"&W$2+$S8)	=INDIRECT("B"&X$2+$S8)
5​	Total	=INDIRECT("B"&U$2+$S9)	=INDIRECT("B"&V$2+$S9)	=INDIRECT("B"&W$2+$S9)	=INDIRECT("B"&X$2+$S9)

 

[bh24524]

Hi, sorry but I am really confused. I tried replicating this and changing cell numbers where I thought they were needed, but I am not sure really what I am supposed to be doing. This is what mine looks like:

This is where I am pasting the data from the export for each week. and below is where I tried to make a table:

I put the value of 8(I'm not sure what that is) in cell C2. I then had the formulas next to it adding 14 starting form C2 and adding 14 to each. I'm not sure what the 14 is either.

In C4 I tried using the following formula:

=INDIRECT('Dairy Data'!B &C$2+$B4)

I know it's wrong since I'm getting a Name error, I'm just not sure what I am actually suppose dot be using. Can you give some more guidance?

 

[bh24524]

Hi, Disregard the above. I did some playing around with it, I was missing the quotes and one of my cell numbers in there was wrong. I needed the formula to be =INDIRECT("'Dairy Data'!B" &C$2+$A4) This is really handy as all I have to do is simply paste the data right below the previous and it picks it up. I've never really worked with Indirect formulas before but this is an indication that they seem very useful. Thanks for the help!

 

[bh24524]

Okay so I do actually have another question regarding this. How can I reverse it so that I can paste the latest data at the top in the Dairy data page. Currently, I am pasting it below the last data set from two weeks ago when we hit a new week. I suppose I could just "Hide" the data on that tab from previous weeks, but it just seemed better if I could paste that most current week of data at the top. I'm not sure if I'd have to tweak the formulas a bit or what would be involved, so if you could let me know?

 

[sijpie]

Sorry for the delay;

If you give the cell 'Let Dows' for the oldest week a name (say, 'Week1'), then you can refer to that name and get the row number and use that in your formulas. Everytime you add rows for a new week, then the row numbers automatically update in the formulas.

So where in my table above in cell U2 I have 8 above the 17th Nov, that would be =ROW(Week1)
Then the cells to the left would contain =U2-14, etc

 

[bh24524]

Sorry but I'm not getting this at all. Can you give more guidance? I just can't follow this when I have the chart on one tab and the actual data on another tab.

I'm just not able to follow based on the above description you gave so maybe we can try and see if this helps?

 

[sijpie]

Sorry for not replying earlier

Assuming that in sheet Dairy Data you have the oldest week at the bottom and the latest week at the top, and also that of the oldest week you name the first cell (in your example the cell with 'Let Down' for that week) 'Week1', then in Dairy Chart sheet you have this:

	(Row)	77​	63​	49​	35​	21​	7​	-	-	-
	Week Ending	30-11-2019​	7-12-2019​	14-12-2019​	21-12-2019​	28-12-2019​	4-1-2020​	11-1-2020​	18-1-2020​	25-1-2020​
0​	Let Down	73​	70​	64​	70​	62​	0​	-	-	-
1​	Loading	113​	87​	81​	80​	110​	62​	-	-	-
2​	Order Selection	94​	93​	95​	99​	95​	123​	-	-	-
3​	Put Away	84​	93​	87​	82​	82​	91​	-	-	-
4​	Receiving	91​	105​	85​	108​	89​	91​	-	-	-

with the following formulae:

	(Row)	=ROW(Week1)	=IF(ISERROR(C2-14),"-",IF(C2-14<0,"-",C2-14))	Copy Right	--->
	Week Ending	30-11-2019​	=+C3+7	Copy Right	--->
0​	Let Down	=IF(C$2<>"-",INDIRECT("'dairy data'!B"&C$2+$A4),"-")	Copy Right​	--->
1​	Loading	Copy down​	Copy Right​	--->
2​	Order Selection	|​	Copy Right​	--->
3​	Put Away	|​	Copy Right​	--->
4​	Receiving	\/​	Copy Right​	--->

 

[bh24524]

Okay I didn't realize you meant using the Name Manager in the dairy data tab. You were talking about this correct?

It looks like it populated the first set of data on the chart tab for the top. In the Dairy Data tab, I had to reorder the data so that the oldest week does appear at the bottom and the latest week at the top. I copied your formulas. In my case now the oldest date in the chart would be week ending 1/11/20. This is what I get in the chart tab:

As you can see, it has pulled the numbers from what is week ending 2/15/20 from the dairy data tab and in the chart tab it has those numbers sitting in the week ending 1/11/20 column. Just wondering what was missed?

 

[sijpie]

OK, so the cell Week1 should be A78 (where the data for 1/11/20 is held)