Hi
I have a graph/ Chart I would like to make if possible.
I would want to be able to select by department and by month and have it display the figure for that
There is 6 departments then I would like to select between any month or the yearly total.
Columns C,E,G,I,J,M,O,Q,S,U,W,Y would all be hidden but are I figured i would need the figure as a percentage rather than a number.
i would like to have the Graph/ Chart on a front sheet so the information can be displayed separate
Hopefully someone can point me in the correct direction
Thanks for any Help
I have a graph/ Chart I would like to make if possible.
I would want to be able to select by department and by month and have it display the figure for that
There is 6 departments then I would like to select between any month or the yearly total.
Columns C,E,G,I,J,M,O,Q,S,U,W,Y would all be hidden but are I figured i would need the figure as a percentage rather than a number.
i would like to have the Graph/ Chart on a front sheet so the information can be displayed separate
Hopefully someone can point me in the correct direction
Thanks for any Help
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C4,Y16,W16,U16,S16,Q16,O16,M16,K16,I16,G16,E16,C16,Y10,W10,U10,S10,Q10,O10,M10,K10,I10,G10,E10,C10,Y4,W4,U4,S4,Q4,O4,M4,K4,I4,G4,E4 | C4 | =SUM((B4-B5-B6)/B4) |
| C5,Y17,W17,U17,S17,Q17,O17,M17,K17,I17,G17,E17,C17,Y11,W11,U11,S11,Q11,O11,M11,K11,I11,G11,E11,C11,Y5,W5,U5,S5,Q5,O5,M5,K5,I5,G5,E5 | C5 | =SUM(B5/B4) |
| C6,Y18,W18,U18,S18,Q18,O18,M18,K18,I18,G18,E18,C18,Y12,W12,U12,S12,Q12,O12,M12,K12,I12,G12,E12,C12,Y6,W6,U6,S6,Q6,O6,M6,K6,I6,G6,E6 | C6 | =SUM(B6/B4) |
| B4,X16,V16,T16,R16,P16,N16,L16,J16,H16,F16,D16,X4,V4,T4,R4,P4,N4,L4,J4,H4,F4,D4 | 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,X5,V5,T5,R5,P5,N5,L5,J5,H5,F5,D5 | 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,X18,V18,T18,R18,P18,N18,L18,J18,H18,F18,D18,X6,V6,T6,R6,P6,N6,L6,J6,H6,F6,D6 | 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,AA19,X19,V19,T19,R19,P19,N19,L19,J19,H19,F19,D19,B19,AA13,X13,V13,T13,R13,P13,N13,L13,J13,H13,F13,D13,B13,AA7,X7,V7,T7,R7,P7,N7,L7,J7,H7,F7,D7 | B7 | =1-(B5/B4) |
| AA4:AA6,AA16:AA18,AA10:AA12 | AA4 | =SUM(B4:X4) |
| B10,X10,V10,T10,R10,P10,N10,L10,J10,H10,F10,D10 | 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,X11,V11,T11,R11,P11,N11,L11,J11,H11,F11,D11 | 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,X12,V12,T12,R12,P12,N12,L12,J12,H12,F12,D12 | 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,X17,V17,T17,R17,P17,N17,L17,J17,H17,F17,D17 | 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 | |
| AA25,AA19 | Cell Value | <0.9799 | text | NO |
| AA25,AA19 | Cell Value | >0.98 | text | NO |
| AA13 | Cell Value | <0.9799 | text | NO |
| AA13 | Cell Value | >0.98 | text | NO |
| AA7 | Cell Value | <0.9799 | text | NO |
| AA7 | Cell Value | >0.98 | text | NO |
| B19:Y19 | Cell Value | <0.9799 | text | NO |
| B19:Y19 | Cell Value | >0.98 | text | NO |
| B7:Y7 | Cell Value | <0.9799 | text | NO |
| B7:Y7 | Cell Value | >0.98 | text | NO |
| B13:Y13 | Cell Value | <0.9799 | text | NO |
| B13:Y13 | Cell Value | >0.98 | text | NO |
