i have formulas that counts how many lines are shipping per day. i want to count how many orders are on my data sheet. the data is entered by line so i have "duplicate" sales order numbers in my column. i was wondering if there was a way to do so.
_______________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H127:H147 | H127 | =I127 |
| I127 | I127 | =COUNTIF(AcuityData!G:G,"="&TODAY()) |
| J127:J147 | J127 | =I127-O127-K127-P127 |
| K127 | K127 | =COUNTIFS(AcuityData!C:C,"BACKORDER",AcuityData!G:G,"="&TODAY()) |
| L127 | L127 | =COUNTIFS(AcuityData!C:C,"PULLED/ NOT DONE",AcuityData!G:G,"="&TODAY()) |
| M127 | M127 | =COUNTIFS(AcuityData!C:C,"Not Pulled",AcuityData!G:G,"="&TODAY()) |
| N127 | N127 | =COUNTIFS(AcuityData!C:C,"PAINTING",AcuityData!G:G,"="&TODAY()) |
| O127 | O127 | =COUNTIFS(AcuityData!C:C,"Shipped",AcuityData!G:G,"="&TODAY()) |
| P127 | P127 | =COUNTIFS(AcuityData!C:C,"CANCELLED",AcuityData!G:G,"="&TODAY()) |
| I128:I147 | I128 | =COUNTIF(AcuityData!G:G,"="&WORKDAY(A127,1)) |
| K128:K147 | K128 | =COUNTIFS(AcuityData!C:C,"BACKORDER",AcuityData!G:G,"="&WORKDAY(A127,1)) |
| L128:L147 | L128 | =COUNTIFS(AcuityData!C:C,"PULLED/ NOT DONE",AcuityData!G:G,"="&WORKDAY(A127,1)) |
| M128:M147 | M128 | =COUNTIFS(AcuityData!C:C,"Not Pulled",AcuityData!G:G,"="&WORKDAY(A127,1)) |
| N128:N147 | N128 | =COUNTIFS(AcuityData!C:C,"PAINTING",AcuityData!G:G,"="&WORKDAY(A127,1)) |
| O128:O147 | O128 | =COUNTIFS(AcuityData!C:C,"Shipped",AcuityData!G:G,"="&WORKDAY(A127,1)) |
| P128:P147 | P128 | =COUNTIFS(AcuityData!C:C,"CANCELLED",AcuityData!G:G,"="&WORKDAY(A127,1)) |
| C127:C147 | C127 | =I127/G127 |
| E127:E147 | E127 | =1-C127 |
| D145:D147 | D145 | =D144 |
| A127 | A127 | =TODAY() |
| A128:A147 | A128 | =WORKDAY(A127,1) |
_______________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
| Production Control Visual 1.6.3.xlsm | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | ||||
| 94 | JC | LABEL ROOM | 318166 | 936838 | 0001077010 | 1/9/2023 | P | 1 | 204K7A | 12/29/2022 | |||
| 95 | JC | 938843 | 0001077024 | 1/5/2023 | 1 | 174LFE | 12/28/2022 | ||||||
| 96 | JC | 938843 | 0001077024 | 1/5/2023 | 2 | 144FFH | 12/28/2022 | ||||||
| 97 | JC | LABEL ROOM | 317972 | 938845 | 0001077029 | 1/5/2023 | P | 1 | 174LFE | 12/28/2022 | |||
| 98 | JC | LABEL ROOM | 317973 | 938845 | 0001077029 | 1/5/2023 | P | 2 | 144FFH | 12/28/2022 | |||
| 99 | JC | LABEL ROOM | 317974 | 938848 | 0001077032 | 1/5/2023 | P | 1 | 174LFE | 12/28/2022 | |||
| 100 | JC | LABEL ROOM | 317975 | 938848 | 0001077032 | 1/5/2023 | P | 2 | 144FFH | 12/28/2022 | |||
| 101 | JC | 938851 | 0001077033 | 1/5/2023 | 1 | 174LFE | 12/28/2022 | ||||||
| 102 | JC | 938851 | 0001077033 | 1/5/2023 | 2 | 144FFH | 12/28/2022 | ||||||
| 103 | JC | 938893 | 0001077059 | 1/5/2023 | 1 | 228YRY | 12/29/2022 | ||||||
| 104 | JC | 938893 | 0001077059 | 1/5/2023 | 2 | 211MMJ | 12/29/2022 | ||||||
| 105 | JC | 938876 | 0001077062 | 1/9/2023 | 1 | 144X3P | |||||||
| 106 | JC | 938877 | 0001077066 | 1/5/2023 | 1 | 142APG | |||||||
| 107 | JC | 2997406 | 0001077069 | 1/6/2023 | 1 | 167GLX | |||||||
| 108 | JC | 938866 | 0001077076 | 1/5/2023 | 1 | 259RYS | |||||||
| 109 | JC | 938858 | 0001077077 | 1/9/2023 | 1 | 215RF6 | |||||||
| 110 | JC | 938859 | 0001077079 | 1/9/2023 | 1 | 211MGC | |||||||
| 111 | JC | 938863 | 0001077082 | 1/6/2023 | 1 | 109PV5 | |||||||
| 112 | JC | 938863 | 0001077082 | 1/6/2023 | 1 | 109PV5 | |||||||
AcuityData | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H94:H112 | H94 | =IF(C94="LABEL ROOM","P","") |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| C2:K1048576 | Expression | =$C2="BACKORDER" | text | YES |
| C2:K1048576 | Expression | =$C2="CANCELLED" | text | YES |
| C:C | Cell Value | contains "JUST ENTERED" | text | YES |
| C2:K1048576 | Expression | =$C2="JUST ENTERED" | text | YES |
| C:C | Cell Value | contains "LABEL ROOM" | text | YES |
| C2:K1048576 | Expression | =$C2="Problem" | text | YES |
| C:C | Cell Value | contains "Problem" | text | YES |
| C:C | Cell Value | contains "NOT PULLED" | text | YES |
| C:C | Cell Value | contains "PARTIAL SHIP" | text | YES |
| C:C | Cell Value | contains "Small Parts" | text | YES |
| C:C | Cell Value | contains "CANCELLED" | text | YES |
| C:C | Cell Value | contains "PAINTING" | text | YES |
| C:C | Cell Value | contains "SHIPPED" | text | YES |
| C:C | Cell Value | contains "PULLED/ NOT DONE" | text | YES |
| C:C | Cell Value | contains "BACKORDER" | text | YES |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| C94:C112 | List | =Dynamic |
