How to Distribute Data in a column/s to other sheets in terms of certain Cell Reference

S

SalamaM

New Member
Joined
Sep 13, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
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
BURAQ8.xlsx
BCDEFGHIJKLMN
1Monthly Monitoring FormSERVICE ZONECODERPumped waterserving days
2WATER DEPARTMENTLELALEL1946213
3Resrevoiur Name:BURAQ RESREVOIRAMALAML6056516
4Department:WATER DEPARTMENTBURAQTADYBDISC9781
5Served ZoneALL ZONES - MAIN METERUN SHELTERESUN2297315
6ARAYSHEYAAR2239316
7TOTAL126371.00m3
8YearMonthStart DateWeekend
92021AUG1No Weekend
10
11P1P2P3P4
12
13
14PUMP METERPUMP DIAL Hours
15DAY DATEWork Hrs STARTENDVOLUMEDISCHARGEBAR CODERP1P2P3P4
16Sun016.071781746002819.0469.83.5AML
17Mon014.074600763761776.0444.02.5AR
18Tue024.076376779171541.0385.33.5UN
19Wed023.07791778895978.0326.03.5BDISC
20Thu034.078895823333438.0859.53.0AML
21Fri036.082333838381505.0250.82.5AR
22Sat046.083838852891451.0241.83.5UN
23Sun046.085289866411352.0225.32.5LEL
24Mon056.086641903333692.0615.33.5AML
25Tue056.090333917821449.0241.52.5AR
26Wed066.091782932211439.0239.83.5UN
27Thu066.093221946881467.0244.53.5LEL
28Fri076.094688977303042.0507.03.5AML
29Sat076.097730992061476.0246.02.5AR
30Sun086.0992061005521346.0224.33.5UN
31Mon086.01005521020171465.0244.23.5LEL
32Tue096.01020171066474630.0771.73.5AML
33Wed096.01066471080941447.0241.23.0AR
34Thu106.01080941096961602.0267.02.5UN
35Fri106.01096961110641368.0228.02.5LEL
36Sat116.01110641141773113.0518.83.5AML
37Sun116.01141771155001323.0220.52.5AR
38Mon126.01155001168601360.0226.73.5UN
39Tue126.01168601181521292.0215.32.5AR
40Wed136.01181521211032951.0491.83.5AML
41Thu136.01211031225881485.0247.52.5AR
42Fri146.01225881240691481.0246.83.5UN
43Sat146.01240691261502081.0346.82.5LEL
44Sun156.01261501306104460.0743.33.5AML
45Mon156.01306101321571547.0257.82.5AR
46Tue166.01321571337831626.0271.03.5UN
47Wed166.01337831350171234.0205.72.5LEL
48Thu176.01350171390504033.0672.23.5AML
49Fri176.01390501403701320.0220.02.5AR
50Sat186.01403701418891519.0253.23.5UN
51Sun186.01418891434391550.0258.33.0LEL
52Mon196.01434391479234484.0747.33.5AML
53Tue196.01479231490721149.0191.52.5AR
54Wed206.01490721506251553.0258.83.5UN
55Thu206.01506251521361511.0251.82.5LEL
56Fri216.01521361565574421.0736.83.5AML
57Sat216.01565571580511494.0249.03.0AR
58Sun226.01580511596011550.0258.32.5UN
59Mon226.01596011610581457.0242.82.5LEL
60Tue236.01610581645003442.0573.73.5AML
61Wed236.01645001655501050.0175.02.5AR
62Thu246.01655501670331483.0247.23.5UN
63Fri246.01670331684821449.0241.53.0LEL
64Sat256.01684821723323850.0641.73.5AML
65Sun256.01723321736511319.0219.82.5AR
66Mon266.01736511755921941.0323.53.5UN
67Tue266.01755921771271535.0255.82.5LEL
68Wed276.01771271821194992.0832.03.5AML
69Thu276.01821191835771458.0243.03.0AR
70Fri286.01835771851201543.0257.23.5UN
71Sat286.01851201864391319.0219.82.5LEL
72Sun296.01864391906374198.0699.73.5AML
73Mon296.01906371919401303.0217.22.5AR
74Tue306.01919401934781538.0256.33.5UN
75Wed306.01934781951521674.0279.03.0LEL
76Thu316.01951521981523000.0500.03.5AML
77
78
79
80
81
82
83Monthly Summary126371.0357.23.1
84
INPUT1
Cell Formulas
RangeFormula
M2:M6M2=COUNTIF(J:J,K2)
L2L2=SUMIFS(G16:G81,J16:J81,"LEL")
L3L3=SUMIFS(G16:G81,J16:J81,"AML")
L4L4=SUMIFS(G16:G81,J16:J81,"BDISC")
L5L5=SUMIFS(G16:G81,J16:J81,"UN")
L6L6=SUMIFS(G16:G81,J16:J81,"AR")
L7L7=SUM(L2:L6)
B16:C16C16=DATE($C$9,MATCH($D$9,MOB.!$B$2:$B$13,0),INPUT1!$E$9)
G16:G76G16=F16-E16
H16:H76H16=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,C20C18=C17+1
F76F76=E76+3000
G83G83=SUM(G16:G81)
H83:I83H83=AVERAGE(H16:H81)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K16:N45,P16:P45,B16:I81Expression=ISNUMBER(SEARCH(TEXT($B16,"ddd"),$F$9))textNO
K46:N76,P46:P76Expression=ISNUMBER(SEARCH(TEXT($B81,"ddd"),$F$9))textNO
Cells with Data Validation
CellAllowCriteria
C9List=MOB.!$A$2:$A$12
D9List=MOB.!$B$2:$B$13
E9List=DateCalc
F9List=MOB.!$D$2:$D$16
 

Attachments

  • INPUT1.png
    INPUT1.png
    161 KB · Views: 16
  • LELA ZONE.png
    LELA ZONE.png
    38 KB · Views: 16

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
You must log in or register to reply here.

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

Share this page

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