Hi
I am wondering if its possible to have a pie chart or something that would work with drop downs
I Have this information thanks to Fluff for sorting me out
id like to be able to filter by location, then month or total for year and have a chart display the corresponding information for me.
the pallets checked is the total for the chart and the incorrect and wrong location are a percentage of that.
is this at all possible and where would i begin
Thanks for any help
I am wondering if its possible to have a pie chart or something that would work with drop downs
I Have this information thanks to Fluff for sorting me out
id like to be able to filter by location, then month or total for year and have a chart display the corresponding information for me.
the pallets checked is the total for the chart and the incorrect and wrong location are a percentage of that.
is this at all possible and where would i begin
Thanks for any help
Perpetual Tracker FG - RM 2022-2023 WIP.xlsx | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
1 | Oct-22 | Nov-22 | Dec-22 | Jan-23 | Feb-23 | Mar-23 | Apr-23 | May-23 | Jun-23 | Jul-23 | Aug-23 | Sep-23 | Year | ||||
2 | |||||||||||||||||
3 | Finished Goods | Finished Goods | |||||||||||||||
4 | Pallets Checked | 3798 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3798 | |||
5 | Pallets Incorrect | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | |||
6 | Pallets wrong Location | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | |||
7 | Accuracy | 99.92% | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | 99.92% | |||
8 | |||||||||||||||||
9 | Board Warehouse | Board Warehouse | |||||||||||||||
10 | Pallets Checked | 2732 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2732 | |||
11 | Pallets Incorrect | 5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | |||
12 | Pallets wrong Location | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | |||
13 | Accuracy | 99.82% | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | 99.82% | |||
14 | |||||||||||||||||
15 | Cold Foil | Cold Foil | |||||||||||||||
16 | Pallets Checked | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||
17 | Pallets Incorrect | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||
18 | Pallets wrong Location | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||
19 | Accuracy | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | |||
Summary Tracker by Month |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B4:M4,C16:M16 | B4 | =SUMIFS('FG Warehouse Perpetual Tracker'!$B$5:$B$40,'FG Warehouse Perpetual Tracker'!$A$5:$A$40,">="&B$1,'FG Warehouse Perpetual Tracker'!$A$5:$A$40,"<="&EOMONTH(B$1,0)) |
B5:M5 | B5 | =SUMPRODUCT(('FG Warehouse Perpetual Tracker'!$A$5:$A$40>=B$1)*('FG Warehouse Perpetual Tracker'!$A$5:$A$40<=EOMONTH(B$1,0))*('FG Warehouse Perpetual Tracker'!$C$5:$D$40)) |
B6:M6,C18:M18 | B6 | =SUMPRODUCT(('FG Warehouse Perpetual Tracker'!$A$5:$A$40>=B$1)*('FG Warehouse Perpetual Tracker'!$A$5:$A$40<=EOMONTH(B$1,0))*('FG Warehouse Perpetual Tracker'!$E$5:$E$40)) |
B7:M7,O19,B19:M19,O13,B13:M13,O7 | B7 | =1-(B5/B4) |
O4:O6,O16:O18,O10:O12 | O4 | =SUM(B4:M4) |
B10:M10 | B10 | =SUMIFS('FG Warehouse Perpetual Tracker'!$M$5:$M$40,'FG Warehouse Perpetual Tracker'!$L$5:$L$40,">="&B$1,'FG Warehouse Perpetual Tracker'!$L$5:$L$40,"<="&EOMONTH(B$1,0)) |
B11:M11 | B11 | =SUMPRODUCT(('FG Warehouse Perpetual Tracker'!$L$5:$L$40>=B$1)*('FG Warehouse Perpetual Tracker'!$L$5:$L$40<=EOMONTH(B$1,0))*('FG Warehouse Perpetual Tracker'!$N$5:$O$40)) |
B12:M12 | B12 | =SUMPRODUCT(('FG Warehouse Perpetual Tracker'!$L$5:$L$40>=B$1)*('FG Warehouse Perpetual Tracker'!$L$5:$L$40<=EOMONTH(B$1,0))*('FG Warehouse Perpetual Tracker'!$P$5:$P$40)) |
B16 | B16 | =SUMIFS('FG Warehouse Perpetual Tracker'!$B$47:$B$82,'FG Warehouse Perpetual Tracker'!$A$47:$A$82,">="&B$1,'FG Warehouse Perpetual Tracker'!$A$47:$A$82,"<="&EOMONTH(B$1,0)) |
B17:M17 | B17 | =SUMPRODUCT(('FG Warehouse Perpetual Tracker'!$A$47:$A$82>=B$1)*('FG Warehouse Perpetual Tracker'!$A$47:$A$82<=EOMONTH(B$1,0))*('FG Warehouse Perpetual Tracker'!$C$47:$D$82)) |
B18 | B18 | =SUMPRODUCT(('FG Warehouse Perpetual Tracker'!$A$47:$A$82>=B$1)*('FG Warehouse Perpetual Tracker'!$A$47:$A$82<=EOMONTH(B$1,0))*('FG Warehouse Perpetual Tracker'!$E$47:$E$82)) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
O25,O19 | Cell Value | <0.9799 | text | NO |
O25,O19 | Cell Value | >0.98 | text | NO |
O13 | Cell Value | <0.9799 | text | NO |
O13 | Cell Value | >0.98 | text | NO |
O7 | Cell Value | <0.9799 | text | NO |
O7 | Cell Value | >0.98 | text | NO |
B19:M19 | Cell Value | <0.9799 | text | NO |
B19:M19 | Cell Value | >0.98 | text | NO |
B7:M7 | Cell Value | <0.9799 | text | NO |
B7:M7 | Cell Value | >0.98 | text | NO |
B13:M13 | Cell Value | <0.9799 | text | NO |
B13:M13 | Cell Value | >0.98 | text | NO |