Making a week by week comparison chart

bh24524

Active Member
Joined
Dec 11, 2008
Messages
365
Office Version
  1. 365
  2. 2007
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:

1575058307046.png


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!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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)
 
Upvote 0
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:

1575646514536.png


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

1575647105034.png


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?
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.

1579005793935.png


1579005822371.png


I'm just not able to follow based on the above description you gave so maybe we can try and see if this helps?
 
Upvote 0
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+7Copy 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​
--->
 
Upvote 0
Okay I didn't realize you meant using the Name Manager in the dairy data tab. You were talking about this correct?

1582115959501.png


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:

1582116305883.png


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?
 
Upvote 0
OK, so the cell Week1 should be A78 (where the data for 1/11/20 is held)
 
Upvote 0

Forum statistics

Threads
1,223,039
Messages
6,169,736
Members
452,279
Latest member
mcecan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top