Dears,
As per discussion on Youtube Channel, please find hereinafter the spreadsheet to help in find out a method to handle the DATA redistribution over other sheets.
How to distribute DATA in Sheets (INPUT1 + INPUT2)
based on Columns J " CODER" over the the other sub sheets {LELA, AMAL, BURAQ SOUTH, UN SHELTERES, ARAYSHEYA}.
Hope to help me in this topic.
Thak you
As per discussion on Youtube Channel, please find hereinafter the spreadsheet to help in find out a method to handle the DATA redistribution over other sheets.
How to distribute DATA in Sheets (INPUT1 + INPUT2)
based on Columns J " CODER" over the the other sub sheets {LELA, AMAL, BURAQ SOUTH, UN SHELTERES, ARAYSHEYA}.
Hope to help me in this topic.
Thak you
BURAQ8.xlsx | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | |||
1 | Monthly Monitoring Form | SERVICE ZONE | CODER | Pumped water | serving days | ||||||||||
2 | WATER DEPARTMENT | LELA | LEL | 19462 | 13 | ||||||||||
3 | Resrevoiur Name: | BURAQ RESREVOIR | AMAL | AML | 60565 | 16 | |||||||||
4 | Department: | WATER DEPARTMENT | BURAQTADY | BDISC | 978 | 1 | |||||||||
5 | Served Zone | ALL ZONES - MAIN METER | UN SHELTERES | UN | 22973 | 15 | |||||||||
6 | ARAYSHEYA | AR | 22393 | 16 | |||||||||||
7 | TOTAL | 126371.00 | m3 | ||||||||||||
8 | Year | Month | Start Date | Weekend | |||||||||||
9 | 2021 | AUG | 1 | No Weekend | |||||||||||
10 | |||||||||||||||
11 | P1 | P2 | P3 | P4 | |||||||||||
12 | |||||||||||||||
13 | |||||||||||||||
14 | PUMP METER | PUMP DIAL Hours | |||||||||||||
15 | DAY | DATE | Work Hrs | START | END | VOLUME | DISCHARGE | BAR | CODER | P1 | P2 | P3 | P4 | ||
16 | Sun | 01 | 6.0 | 71781 | 74600 | 2819.0 | 469.8 | 3.5 | AML | ||||||
17 | Mon | 01 | 4.0 | 74600 | 76376 | 1776.0 | 444.0 | 2.5 | AR | ||||||
18 | Tue | 02 | 4.0 | 76376 | 77917 | 1541.0 | 385.3 | 3.5 | UN | ||||||
19 | Wed | 02 | 3.0 | 77917 | 78895 | 978.0 | 326.0 | 3.5 | BDISC | ||||||
20 | Thu | 03 | 4.0 | 78895 | 82333 | 3438.0 | 859.5 | 3.0 | AML | ||||||
21 | Fri | 03 | 6.0 | 82333 | 83838 | 1505.0 | 250.8 | 2.5 | AR | ||||||
22 | Sat | 04 | 6.0 | 83838 | 85289 | 1451.0 | 241.8 | 3.5 | UN | ||||||
23 | Sun | 04 | 6.0 | 85289 | 86641 | 1352.0 | 225.3 | 2.5 | LEL | ||||||
24 | Mon | 05 | 6.0 | 86641 | 90333 | 3692.0 | 615.3 | 3.5 | AML | ||||||
25 | Tue | 05 | 6.0 | 90333 | 91782 | 1449.0 | 241.5 | 2.5 | AR | ||||||
26 | Wed | 06 | 6.0 | 91782 | 93221 | 1439.0 | 239.8 | 3.5 | UN | ||||||
27 | Thu | 06 | 6.0 | 93221 | 94688 | 1467.0 | 244.5 | 3.5 | LEL | ||||||
28 | Fri | 07 | 6.0 | 94688 | 97730 | 3042.0 | 507.0 | 3.5 | AML | ||||||
29 | Sat | 07 | 6.0 | 97730 | 99206 | 1476.0 | 246.0 | 2.5 | AR | ||||||
30 | Sun | 08 | 6.0 | 99206 | 100552 | 1346.0 | 224.3 | 3.5 | UN | ||||||
31 | Mon | 08 | 6.0 | 100552 | 102017 | 1465.0 | 244.2 | 3.5 | LEL | ||||||
32 | Tue | 09 | 6.0 | 102017 | 106647 | 4630.0 | 771.7 | 3.5 | AML | ||||||
33 | Wed | 09 | 6.0 | 106647 | 108094 | 1447.0 | 241.2 | 3.0 | AR | ||||||
34 | Thu | 10 | 6.0 | 108094 | 109696 | 1602.0 | 267.0 | 2.5 | UN | ||||||
35 | Fri | 10 | 6.0 | 109696 | 111064 | 1368.0 | 228.0 | 2.5 | LEL | ||||||
36 | Sat | 11 | 6.0 | 111064 | 114177 | 3113.0 | 518.8 | 3.5 | AML | ||||||
37 | Sun | 11 | 6.0 | 114177 | 115500 | 1323.0 | 220.5 | 2.5 | AR | ||||||
38 | Mon | 12 | 6.0 | 115500 | 116860 | 1360.0 | 226.7 | 3.5 | UN | ||||||
39 | Tue | 12 | 6.0 | 116860 | 118152 | 1292.0 | 215.3 | 2.5 | AR | ||||||
40 | Wed | 13 | 6.0 | 118152 | 121103 | 2951.0 | 491.8 | 3.5 | AML | ||||||
41 | Thu | 13 | 6.0 | 121103 | 122588 | 1485.0 | 247.5 | 2.5 | AR | ||||||
42 | Fri | 14 | 6.0 | 122588 | 124069 | 1481.0 | 246.8 | 3.5 | UN | ||||||
43 | Sat | 14 | 6.0 | 124069 | 126150 | 2081.0 | 346.8 | 2.5 | LEL | ||||||
44 | Sun | 15 | 6.0 | 126150 | 130610 | 4460.0 | 743.3 | 3.5 | AML | ||||||
45 | Mon | 15 | 6.0 | 130610 | 132157 | 1547.0 | 257.8 | 2.5 | AR | ||||||
46 | Tue | 16 | 6.0 | 132157 | 133783 | 1626.0 | 271.0 | 3.5 | UN | ||||||
47 | Wed | 16 | 6.0 | 133783 | 135017 | 1234.0 | 205.7 | 2.5 | LEL | ||||||
48 | Thu | 17 | 6.0 | 135017 | 139050 | 4033.0 | 672.2 | 3.5 | AML | ||||||
49 | Fri | 17 | 6.0 | 139050 | 140370 | 1320.0 | 220.0 | 2.5 | AR | ||||||
50 | Sat | 18 | 6.0 | 140370 | 141889 | 1519.0 | 253.2 | 3.5 | UN | ||||||
51 | Sun | 18 | 6.0 | 141889 | 143439 | 1550.0 | 258.3 | 3.0 | LEL | ||||||
52 | Mon | 19 | 6.0 | 143439 | 147923 | 4484.0 | 747.3 | 3.5 | AML | ||||||
53 | Tue | 19 | 6.0 | 147923 | 149072 | 1149.0 | 191.5 | 2.5 | AR | ||||||
54 | Wed | 20 | 6.0 | 149072 | 150625 | 1553.0 | 258.8 | 3.5 | UN | ||||||
55 | Thu | 20 | 6.0 | 150625 | 152136 | 1511.0 | 251.8 | 2.5 | LEL | ||||||
56 | Fri | 21 | 6.0 | 152136 | 156557 | 4421.0 | 736.8 | 3.5 | AML | ||||||
57 | Sat | 21 | 6.0 | 156557 | 158051 | 1494.0 | 249.0 | 3.0 | AR | ||||||
58 | Sun | 22 | 6.0 | 158051 | 159601 | 1550.0 | 258.3 | 2.5 | UN | ||||||
59 | Mon | 22 | 6.0 | 159601 | 161058 | 1457.0 | 242.8 | 2.5 | LEL | ||||||
60 | Tue | 23 | 6.0 | 161058 | 164500 | 3442.0 | 573.7 | 3.5 | AML | ||||||
61 | Wed | 23 | 6.0 | 164500 | 165550 | 1050.0 | 175.0 | 2.5 | AR | ||||||
62 | Thu | 24 | 6.0 | 165550 | 167033 | 1483.0 | 247.2 | 3.5 | UN | ||||||
63 | Fri | 24 | 6.0 | 167033 | 168482 | 1449.0 | 241.5 | 3.0 | LEL | ||||||
64 | Sat | 25 | 6.0 | 168482 | 172332 | 3850.0 | 641.7 | 3.5 | AML | ||||||
65 | Sun | 25 | 6.0 | 172332 | 173651 | 1319.0 | 219.8 | 2.5 | AR | ||||||
66 | Mon | 26 | 6.0 | 173651 | 175592 | 1941.0 | 323.5 | 3.5 | UN | ||||||
67 | Tue | 26 | 6.0 | 175592 | 177127 | 1535.0 | 255.8 | 2.5 | LEL | ||||||
68 | Wed | 27 | 6.0 | 177127 | 182119 | 4992.0 | 832.0 | 3.5 | AML | ||||||
69 | Thu | 27 | 6.0 | 182119 | 183577 | 1458.0 | 243.0 | 3.0 | AR | ||||||
70 | Fri | 28 | 6.0 | 183577 | 185120 | 1543.0 | 257.2 | 3.5 | UN | ||||||
71 | Sat | 28 | 6.0 | 185120 | 186439 | 1319.0 | 219.8 | 2.5 | LEL | ||||||
72 | Sun | 29 | 6.0 | 186439 | 190637 | 4198.0 | 699.7 | 3.5 | AML | ||||||
73 | Mon | 29 | 6.0 | 190637 | 191940 | 1303.0 | 217.2 | 2.5 | AR | ||||||
74 | Tue | 30 | 6.0 | 191940 | 193478 | 1538.0 | 256.3 | 3.5 | UN | ||||||
75 | Wed | 30 | 6.0 | 193478 | 195152 | 1674.0 | 279.0 | 3.0 | LEL | ||||||
76 | Thu | 31 | 6.0 | 195152 | 198152 | 3000.0 | 500.0 | 3.5 | AML | ||||||
77 | |||||||||||||||
78 | |||||||||||||||
79 | |||||||||||||||
80 | |||||||||||||||
81 | |||||||||||||||
82 | |||||||||||||||
83 | Monthly Summary | 126371.0 | 357.2 | 3.1 | |||||||||||
84 | |||||||||||||||
INPUT1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
M2:M6 | M2 | =COUNTIF(J:J,K2) |
L2 | L2 | =SUMIFS(G16:G81,J16:J81,"LEL") |
L3 | L3 | =SUMIFS(G16:G81,J16:J81,"AML") |
L4 | L4 | =SUMIFS(G16:G81,J16:J81,"BDISC") |
L5 | L5 | =SUMIFS(G16:G81,J16:J81,"UN") |
L6 | L6 | =SUMIFS(G16:G81,J16:J81,"AR") |
L7 | L7 | =SUM(L2:L6) |
B16:C16 | C16 | =DATE($C$9,MATCH($D$9,MOB.!$B$2:$B$13,0),INPUT1!$E$9) |
G16:G76 | G16 | =F16-E16 |
H16:H76 | H16 | =G16/D16 |
C18,B76:C76,B17:B75,C74,C72,C70,C68,C66,C64,C62,C60,C58,C56,C54,C52,C50,C48,C46,C44,C42,C40,C38,C36,C34,C32,C30,C28,C26,C24,C22,C20 | C18 | =C17+1 |
F76 | F76 | =E76+3000 |
G83 | G83 | =SUM(G16:G81) |
H83:I83 | H83 | =AVERAGE(H16:H81) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
K16:N45,P16:P45,B16:I81 | Expression | =ISNUMBER(SEARCH(TEXT($B16,"ddd"),$F$9)) | text | NO |
K46:N76,P46:P76 | Expression | =ISNUMBER(SEARCH(TEXT($B81,"ddd"),$F$9)) | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
C9 | List | =MOB.!$A$2:$A$12 |
D9 | List | =MOB.!$B$2:$B$13 |
E9 | List | =DateCalc |
F9 | List | =MOB.!$D$2:$D$16 |