APTS Common Expenses 2020.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | 22/6/2020 | ||||||||
2 | ΔΙΑΜΕΡΙΣΜΑ 01 | ||||||||
3 | ΗΜΕΡΟΜΗΝΙΑ | ΠΕΡΙΓΡΑΦΗ | ΟΦΕΙΛΟΜΕΝΟ ΠΟΣΟ | ΠΛΗΡΩΘΕΝ ΠΟΣΟ | ΥΠΟΛΟΙΠΟ | ||||
4 | 1/1/2020 | ΥΠΟΛΟΙΠΟ 2019 | 0,00 | 1 | |||||
5 | 5/1/2020 | ΙΑΝ | -5 | 5 | 0,00 | 2 | |||
6 | 5/3/2020 | ΦΕΒ | -5 | 6 | 1,00 | 3 | |||
7 | 10/6/2020 | ΜΑΡ | -5 | 7 | 3,00 | 4 | |||
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 | ||
---|---|---|
Range | Formula | |
A1 | A1 | =TODAY() |
A5:A22 | A5 | =IFERROR(IF(INDEX(myΗΜΕΡΟΜΗΝΙΑ,MATCH(ROW(A1),APT_01,0),1)>0,INDEX(myΗΜΕΡΟΜΗΝΙΑ,MATCH(ROW(A1),APT_01,0),1),0),"") |
E5:E22 | E5 | =IF(A5<>"",ROUND(SUM((E4+C5)+D5),2)) |
G4:G22 | G4 | =IF(A4="","",IF(A4<=$A$1,ROW()-3,"")) |
D5:D22 | D5 | =IFERROR(IF(INDEX(myΠΟΣΟ,MATCH(ROW(A1),APT_01,0),1)>0,INDEX(myΠΟΣΟ,MATCH(ROW(A1),APT_01,0),1),0),"") |
D23 | D23 | =SUM(D5:D22) |
B24 | B24 | =IF(E24<0,"ΟΦΕΙΛΟΜΕΝΟ ΥΠΟΛΟΙΠΟ",IF(E24=0,"--------------------------",IF(E24>0,"ΠΡΟΠΛΗΡΩΜΗ"))) |
E24 | E24 | =OFFSET($G$4,MATCH(LARGE($G$5:$G$22,1),$G$5:$G$22,0),-2) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
'APT 01'!Print_Area | ='APT 01'!$A$2:$E$24 | D6, A6 |
APTS Common Expenses 2020.xlsx | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | ΗΜΕΡΟΜΗΝΙΑ | ΠΕΡΙΓΡΑΦΗ | ΠΟΣΟ | ΔΙΑΜ 01 | ||
2 | 5/1/2020 | ΔΙΑΜ 01 | 5 | 1 | ||
3 | 5/3/2020 | ΔΙΑΜ 01 | 6 | 2 | ||
4 | 10/6/2020 | ΔΙΑΜ 01 | 7 | 3 | ||
BANK ACCOUNT STATEMENT |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2:D4 | D2 | =IF($B2=D$1,COUNTIF($B$2:$B2,$B2),0) |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
B2:B4 | List | =ΕΠΙΛΟΓΕΣ |
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,