MrDB4Excel
Active Member
- Joined
- Jan 29, 2004
- Messages
- 348
- Office Version
- 2013
- Platform
- Windows
I have spent the better part of today trying all sorts of formulas and searching the web for an answer and I just don’t seem to be able to figure this to a satisfactory conclusion. Any help will be very much appreciated.
I have a series of sheets within a workbook that detail building fees that are paid monthly with specific static amounts that at the end of the year are paired against actual fees as seen in the accompanying Xl2bb Mini Sheets thus providing a refund or payment due.
In cell B2, if there is a refund due because of overpayment during the year then cell B2 will show Refund Received plus a date per the date that exists in cell N2; otherwise it must remain blank and show nothing.
In cell B3, if I need to make a payment because building expenses were more during the year than what I paid by the month then cell B3 would show Payment Made plus a date per the date that exists in cell N2; otherwise it must remain blank and show nothing.
In short only one of the two cells B2 & B3 will show data depending on what data exists in C24 or C26.
I have provided two Mini Sheets of Xl2bb, one for sheet 2015 and one for sheet 2018.
Sheet 2015 shows a refund of 4,532 Kč is due and sheet 2018 shows I need to make a payment of 922 Kč to bring my account out of arrears.
Obviously, these are past tense, meaning refunds and payments have been made. I am merely setting up this workbook for future years based on existing past data. Included in this workbook are additional sheets 2016, 2017, 2019, 2020, 2021, & 2022.
Getting this all to work will drastically reduce my time involved for future years.
Sheet 2015
Sheet 2018
I have a series of sheets within a workbook that detail building fees that are paid monthly with specific static amounts that at the end of the year are paired against actual fees as seen in the accompanying Xl2bb Mini Sheets thus providing a refund or payment due.
In cell B2, if there is a refund due because of overpayment during the year then cell B2 will show Refund Received plus a date per the date that exists in cell N2; otherwise it must remain blank and show nothing.
In cell B3, if I need to make a payment because building expenses were more during the year than what I paid by the month then cell B3 would show Payment Made plus a date per the date that exists in cell N2; otherwise it must remain blank and show nothing.
In short only one of the two cells B2 & B3 will show data depending on what data exists in C24 or C26.
I have provided two Mini Sheets of Xl2bb, one for sheet 2015 and one for sheet 2018.
Sheet 2015 shows a refund of 4,532 Kč is due and sheet 2018 shows I need to make a payment of 922 Kč to bring my account out of arrears.
Obviously, these are past tense, meaning refunds and payments have been made. I am merely setting up this workbook for future years based on existing past data. Included in this workbook are additional sheets 2016, 2017, 2019, 2020, 2021, & 2022.
Getting this all to work will drastically reduce my time involved for future years.
Sheet 2015
| Annual Building Fee Statement.xlsx | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 2 | Refund Received 04 May 2016 | Množství Amount | Spotřeba Consumption | Náklady Costs | 5/4/2016 | |||||||||||
| 3 | na objekt per object | celkové total | Způsob rozúčt. = Way to break down | Podíl % Percentage | na objekt per object | celkové total | na objekt per object | celkové total | Cena/MJ Price/MJ | Zaplaceno Paid | Rozdíl Difference | 04 May 2016 | ||||
| 4 | fond oprav = pool repairs | 298.04 | 16,274.00 | Spoluvlastnický podíl = Co-ownership share | 100.00 | 0.00 | 0.00 | 7,110.00 | 390,576.00 | 0.00 | 7,110.00 | 0.00 | ||||
| 5 | el. energie spol. Prostor ´power??? | 0.42 | 32.00 | Osoby = Persons | 100.00 | 0.00 | 0.00 | 298.69 | 22,801.00 | 0.00 | 100.00 | 198.69 | ||||
| 6 | odvoz odpadu = waste removal | 0.42 | 33.00 | Osoby = Persons | 100.00 | 0.00 | 0.00 | 136.18 | 10,720.00 | 0.00 | 175.00 | -38.82 | ||||
| 7 | otop = One Tambon one product | 3,575.10 | 209,685.80 | Měřidlo = Gauge | 100.00 | 0.00 | 0.00 | 3,575.10 | 209,685.80 | 0.00 | 6,365.00 | -2,789.90 | ||||
| 8 | poplatek za rozúčtování = the fee for the accounting distribution | 261.40 | 9,040.30 | Měřidlo = Gauge | 100.00 | 0.00 | 0.00 | 261.40 | 9,040.30 | 0.00 | 0.00 | 261.40 | ||||
| 9 | TUV = ??? | 0.00 | 161,410.60 | Měřidlo = Gauge | 100.00 | 0.00 | 0.00 | 0.00 | 161,410.60 | 0.00 | 1,500.00 | -1,500.00 | ||||
| 10 | úklíd = cleaning | 0.42 | 33.00 | Osoby = Persons | 100.00 | 0.00 | 0.00 | 180.66 | 14,222.00 | 0.00 | 550.00 | -369.34 | ||||
| 11 | voda = water | 247.40 | 56,223.10 | Měřidlo = Gauge | 100.00 | 0.00 | 0.00 | 247.40 | 56,223.10 | 0.00 | 750.00 | -502.60 | ||||
| 12 | výtah = lift | 0.42 | 32.00 | Osoby = Persons | 100.00 | 0.00 | 0.00 | 508.41 | 38,810.00 | 0.00 | 300.00 | 208.41 | ||||
| 13 | správa = management | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 650.00 | 0.00 | 0.00 | 650.00 | 0.00 | |||||
| 14 | zaokrouhlení = rounding | 1.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.16 | 0.00 | 0.00 | 0.00 | 0.16 | |||||
| 15 | Celkem za objekt Mariánské Lázně-533-201 | Celkem za objekt Mariánské Lázně-533-201 | 12,968.00 | 17,500.00 | -4,532.00 | 4532 | 0.00 | |||||||||
| 16 | vyúčtování = billing | |||||||||||||||
| 17 | ||||||||||||||||
| 18 | Přeplatek = Overpayment | 4,532.00 Kč | ||||||||||||||
| 19 | ||||||||||||||||
| 20 | Nedoplatek = Arrears, Balance Due | 0.00 Kč | ||||||||||||||
| 21 | ||||||||||||||||
| 22 | Stav fondu oprav = Status of the repair pool | |||||||||||||||
| 23 | ||||||||||||||||
| 24 | Refund Received | 4,532.00 Kč | ||||||||||||||
| 25 | ||||||||||||||||
| 26 | Payment Made | |||||||||||||||
2015 | ||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2 | B2 | =IF($C$24>0,$B$24)&" "&$N$3 |
| N3 | N3 | =TEXT($N$2,"dd mmm yyyy") |
| I15,L15:M15 | I15 | =SUM(I4:I14) |
| N15 | N15 | =IF($M$15<0,$M$15*-1,"") |
| O15 | O15 | =SUM($M$15,$N$15) |
| C18 | C18 | =IF($M$15<0,$N$15,"") |
| C20 | C20 | =IF($M$15>0,$M$15,$O$15) |
| C24 | C24 | =IF($C$18=$N$15,$C$18) |
| C26 | C26 | =IF($C$20=0,"",IF($C$20>0,$N$15)) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| C4:D14 | Cell Value | <0 | text | NO |
| F4:M14,I15:M15 | Cell Value | <0 | text | NO |
Sheet 2018
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2 | B2 | =IF($C$24>0,$B$24)&" "&$N$3 |
| G2 | G2 | ='2015'!$G$2:$H$2 |
| I2 | I2 | ='2015'!$I$2:$J$2 |
| C2 | C2 | ='2015'!$C$2:$D$2 |
| C3:M3 | C3 | ='2015'!C$3 |
| N3 | N3 | =TEXT($N$2,"dd mmm yyyy") |
| E4:E12 | E4 | ='2015'!$E4 |
| B26,B24,B4:B14 | B4 | ='2015'!$B4 |
| I15,L15:M15 | I15 | =SUM(I4:I14) |
| N15 | N15 | =IF($M$15<0,$M$15*-1,"") |
| O15 | O15 | =SUM($M$15,$N$15) |
| B18 | B18 | ='2015'!$B$18 |
| C18 | C18 | =IF($M$15<0,$N$15,"") |
| B20 | B20 | ='2015'!$B$20 |
| C20 | C20 | =IF($M$15>0,$M$15,$O$15) |
| C24 | C24 | =IF($C$18=$N$15,$C$18) |
| C26 | C26 | =IF($C$20=0,"",IF($C$20>0,$N$15)) |
| D26 | D26 | =C26 |
| B28 | B28 | =IF(ISBLANK(C26)," ",B27) |
| C28:C30 | C28 | =FORMULATEXT(B28) |
| B29 | B29 | =IF(D26=0,"") |
| B30 | B30 | =IF(D26<>0,B26) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| C4:C14 | Cell Value | <0 | text | NO |
| F4:M15 | Cell Value | <0 | text | NO |
| D4:D14 | Cell Value | <0 | text | NO |
