copy and replace date from other tab in row with the same month

guscy

Board Regular
Joined
Jun 13, 2016
Messages
54
Office Version
  1. 2019
Platform
  1. Windows
APTS Common Expenses 2020.xlsx
ABCDEFG
122/6/2020
2ΔΙΑΜΕΡΙΣΜΑ 01
3ΗΜΕΡΟΜΗΝΙΑΠΕΡΙΓΡΑΦΗΟΦΕΙΛΟΜΕΝΟ ΠΟΣΟΠΛΗΡΩΘΕΝ ΠΟΣΟΥΠΟΛΟΙΠΟ
41/1/2020 ΥΠΟΛΟΙΠΟ 20190,001
55/1/2020ΙΑΝ-550,002
65/3/2020ΦΕΒ-561,003
710/6/2020ΜΑΡ-573,004
8 ΑΠΡ-5 FALSE 
9 ΜΑΪ-5 FALSE 
10 ΙΟΥΝ-5 FALSE 
11 -5 FALSE 
12 ΙΟΥΛ-5 FALSE 
13 ΑΥΓ-5 FALSE 
14 ΣΕΠ-5 FALSE 
15 ΟΚΤ-5 FALSE 
16 ΝΟΕ-5 FALSE 
17 ΔΕΚ-5 FALSE 
18  FALSE 
19  FALSE 
20  FALSE 
21  FALSE 
22  FALSE 
23ΣΥΝΟΛΟ ΠΛΗΡΩΜΩΝ:18
24ΣΥΝΟΛΟ:ΠΡΟΠΛΗΡΩΜΗ3,00
APT 01
Cell Formulas
RangeFormula
A1A1=TODAY()
A5:A22A5=IFERROR(IF(INDEX(myΗΜΕΡΟΜΗΝΙΑ,MATCH(ROW(A1),APT_01,0),1)>0,INDEX(myΗΜΕΡΟΜΗΝΙΑ,MATCH(ROW(A1),APT_01,0),1),0),"")
E5:E22E5=IF(A5<>"",ROUND(SUM((E4+C5)+D5),2))
G4:G22G4=IF(A4="","",IF(A4<=$A$1,ROW()-3,""))
D5:D22D5=IFERROR(IF(INDEX(myΠΟΣΟ,MATCH(ROW(A1),APT_01,0),1)>0,INDEX(myΠΟΣΟ,MATCH(ROW(A1),APT_01,0),1),0),"")
D23D23=SUM(D5:D22)
B24B24=IF(E24<0,"ΟΦΕΙΛΟΜΕΝΟ ΥΠΟΛΟΙΠΟ",IF(E24=0,"--------------------------",IF(E24>0,"ΠΡΟΠΛΗΡΩΜΗ")))
E24E24=OFFSET($G$4,MATCH(LARGE($G$5:$G$22,1),$G$5:$G$22,0),-2)
Named Ranges
NameRefers ToCells
'APT 01'!Print_Area='APT 01'!$A$2:$E$24D6, A6


APTS Common Expenses 2020.xlsx
ABCD
1ΗΜΕΡΟΜΗΝΙΑΠΕΡΙΓΡΑΦΗΠΟΣΟΔΙΑΜ 01
25/1/2020ΔΙΑΜ 0151
35/3/2020ΔΙΑΜ 0162
410/6/2020ΔΙΑΜ 0173
BANK ACCOUNT STATEMENT
Cell Formulas
RangeFormula
D2:D4D2=IF($B2=D$1,COUNTIF($B$2:$B2,$B2),0)
Cells with Data Validation
CellAllowCriteria
B2:B4List=ΕΠΙΛΟΓΕΣ


Hi,

Date is copy from tab BANK ACCOUNT STATEMENT in TAB APT 01 column A.
I need in tab APT 01 column A to have pre-enter dates starting from A5 with 1/1/2020, A6 1/2/2020 to 1/12/2020 and then if a payment is made date from tab BANK ACCOUNT STATEMENT to replace pre-enter date but ir row with same month. Others date must not affected.
Is that possible?

Thanks in advance,
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

guscy

Board Regular
Joined
Jun 13, 2016
Messages
54
Office Version
  1. 2019
Platform
  1. Windows
APTS Common Expenses 2020_FINAL.xlsx
ABCDEFG
125/6/2020
2ΔΙΑΜΕΡΙΣΜΑ 01
3ΗΜΕΡΟΜΗΝΙΑΠΕΡΙΓΡΑΦΗΟΦΕΙΛΟΜΕΝΟ ΠΟΣΟΠΛΗΡΩΘΕΝ ΠΟΣΟΥΠΟΛΟΙΠΟ
41/1/2020 ΥΠΟΛΟΙΠΟ 20192,001
52/5/2020ΙΑΝ-28,99303,012
63/5/2020ΦΕΒ-28,99304,023
77/5/2020ΜΑΡ-28,99305,034
89/5/2020ΑΠΡ-28,99306,045
9 ΜΑΪ-28,99   
10 ΙΟΥΝ-28,99   
11    
12 ΙΟΥΛ-28,99   
13 ΑΥΓ-28,99   
14 ΣΕΠ-28,99   
15 ΟΚΤ-28,99   
16 ΝΟΕ-28,99   
17 ΔΕΚ-28,99   
18    
19    
20    
21    
22    
23ΣΥΝΟΛΟ ΠΛΗΡΩΜΩΝ:120
24ΣΥΝΟΛΟ:ΠΡΟΠΛΗΡΩΜΗ6,04
APT 01
Cell Formulas
RangeFormula
A1A1=TODAY()
A5:A17A5=IFERROR(IF(INDEX(myΗΜΕΡΟΜΗΝΙΑ,MATCH(ROW(A1),ΔΙΑΜ_01,0),1)>0,INDEX(myΗΜΕΡΟΜΗΝΙΑ,MATCH(ROW(A1),ΔΙΑΜ_01,0),1),0),"")
A18:A22A18=IFERROR(IF(INDEX(myΗΜΕΡΟΜΗΝΙΑ,MATCH(ROW(A14),APT_01,0),1)>0,INDEX(myΗΜΕΡΟΜΗΝΙΑ,MATCH(ROW(A14),APT_01,0),1),0),"")
E5:E22E5=IF(A5<>"",ROUND(SUM((E4+C5)+D5),2),"")
G4:G22G4=IF(A4="","",IF(A4<=$A$1,ROW()-3,""))
D5:D22D5=IFERROR(IF(INDEX(myΠΟΣΟ,MATCH(ROW(A1),ΔΙΑΜ_01,0),1)>0,INDEX(myΠΟΣΟ,MATCH(ROW(A1),ΔΙΑΜ_01,0),1),0),"")
D23D23=SUM(D5:D22)
B24B24=IF(E24<0,"ΟΦΕΙΛΟΜΕΝΟ ΥΠΟΛΟΙΠΟ",IF(E24=0,"--------------------------",IF(E24>0,"ΠΡΟΠΛΗΡΩΜΗ")))
E24E24=IFERROR(OFFSET($G$4,MATCH(LARGE($G$5:$G$22,1),$G$5:$G$22,0),-2),E4)


APTS Common Expenses 2020_FINAL.xlsx
ABCDEFGHI
1ΗΜΕΡΟΜΗΝΙΑΠΕΡΙΓΡΑΦΗΠΟΣΟΔΙΑΜ 01ΔΙΑΜ 02ΔΙΑΜ 11ΔΙΑΜ 12ΔΙΑΜ 13ΔΙΑΜ 14
22/5/2020ΔΙΑΜ 0130100000
33/5/2020ΔΙΑΜ 0130200000
46/5/2020ΛΟΓΑΡΙΑΣΜΟΣ ΡΕΥΜΑΤΟΣ20000000
57/5/2020ΔΙΑΜ 0130300000
69/5/2020ΔΙΑΜ 0130400000
7000000
8000000
9000000
10000000
11000000
12000000
13000000
14000000
15000000
16000000
17000000
18000000
19000000
20000000
21000000
22000000
23000000
24000000
25000000
26000000
27000000
28000000
29000000
30000000
31000000
32000000
33000000
34000000
35000000
36000000
37000000
38000000
39000000
40000000
41000000
42000000
43000000
44000000
45000000
46000000
47000000
48000000
49000000
50000000
51000000
52000000
53000000
54000000
55000000
56000000
57000000
58000000
59000000
60000000
61000000
62000000
63000000
64000000
65000000
66000000
67000000
68000000
69000000
70000000
71000000
72000000
73000000
74000000
75000000
76000000
77000000
78000000
79000000
80000000
81000000
82000000
83000000
84000000
85000000
86000000
87000000
88000000
89000000
90000000
91000000
92000000
93000000
94000000
95000000
96000000
97000000
98000000
99000000
100000000
BANK ACCOUNT STATEMENT
Cell Formulas
RangeFormula
D2:I100D2=IF($B2=D$1,COUNTIF($B$2:$B2,$B2),0)
Cells with Data Validation
CellAllowCriteria
B2:B100List=ΕΠΙΛΟΓΕΣ


Hi,

I am using excel to manage the income and outcome payments of a small building with total 6 apartments.

In tab BANK ACCOUNT STATEMENT, I enter manually selecting from a list all income and outcome payments.

Then, from there, income payment with date and amount is also enter using formulas in the correct APT tab, APT 01, 02, 11, 12, 13, 14. In attached fie you can see it in APT 01.

Desired output is to combine tab APT 01 and APT 01_2. In tab APT 01, I need dates of amount due to be typed in column A and automatically change to the real date when payment was made. For example, if due date is 5/6/2020 and there is a payment according to BANK STATEMENT ACCOUNT Tab, on 8/5/2020, then date 5/6/2020 should change to 8/5/2020 without affecting other dates. Also, payment amount in column D should be in same row with payment date.

In addition, in Tab APT 01, column E, I need to have display the amount due like APT01_2 tab because I use formula in G column to know any moment in E24 how much an apartment due or has prepaid. Tab 01_2 only for explanation.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,073
Messages
5,545,832
Members
410,709
Latest member
Mrsamir
Top