4hoteliers
New Member
- Joined
- Jan 18, 2016
- Messages
- 44
| Balance.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | Account | Payee | Category | reconcile or clear | Payment | Deposit | Cleared Payment | Cleared Deposit | Account Balance | Cleared Balance | Balance | BANK NAME | EACH BANK balance | ||
| 2 | Balance EUROBANK | Balance - EUROBANK | [Balance - EUROBANK | c | 15000 | 0 | 15000 | 15,000.00 | 15,000.00 | 15,000.00 | EUROBANK | ||||
| 3 | Balance ALPHA BAK | Balance - ALPHA BANK | [Balance - ALPHA BANK | c | 8500 | 0 | 8500 | 8,500.00 | 8,500.00 | 23,500.00 | ALPHA BANK | ||||
| 4 | Cash | Interest | INTEREST - EUROBANK | c | 0.12 | 0 | 0.12 | 0.12 | 0.12 | 23,500.12 | EUROBANK | ||||
| 5 | Cash | Inteest Tax | INTEREST TAX - EUROBANK | c | 0.02 | 0.02 | 0 | 0.10 | 0.10 | 23,500.10 | EUROBANK | ||||
| 6 | Web Banking | Vodafone | Phone VODAFONE - EUROBANK | c | 21.61 | 21.61 | 0 | (21.61) | (21.61) | 23,478.49 | EUROBANK | ||||
| 7 | Web Banking | Shared Utility Monthly Bill | Shared Utility Monthly Bill - ALPHA | c | 39.33 | 39.33 | 0 | (60.94) | (60.94) | 23,439.16 | ALPHA | ||||
| 8 | Web Banking | Office funriture Buy | Office Furniture Buy - EUROBANK | c | 73.75 | 73.75 | 0 | (134.69) | (134.69) | 23,365.41 | EUROBANK | ||||
| 9 | Web Banking | Software Buy | Software Buy - ALPHA | c | 104.13 | 104.13 | 0 | (238.82) | (238.82) | 23,261.28 | ALPHA | ||||
| 10 | Web Banking | Car Leasing | Car Leasing - EUROBANK | c | 447.64 | 447.64 | 0 | (686.46) | (686.46) | 22,813.64 | EUROBANK | ||||
| 11 | Client Deposit | Client 1 Deposit | THIRA'S DOLPHIN - EUROBANK | c | 1000 | 0 | 1000 | 1,000.00 | 1,000.00 | 23,813.64 | EUROBANK | ||||
| 12 | Cash | Withraw cash | Withdraw - Eurobank | c | 400 | 400 | 0 | (399.90) | (399.90) | 23,413.64 | Eurobank | ||||
| 13 | Cash | Withraw cash | Withdraw - ALPHA | c | 200 | 200 | 0 | (599.90) | (599.90) | 23,213.64 | ALPHA | ||||
| 14 | Web Banking | Acount Payment | ACCOUNTANT PAYMENT - EUROBANK | c | 286.44 | 286.44 | 0 | (972.90) | (972.90) | 22,927.20 | EUROBANK | ||||
| 15 | Web Banking | Annual Advertisitng | Annual Advertising Hotelier Academy - EUROBANK | c | 3300 | 3300 | 0 | (4,272.90) | (4,272.90) | 19,627.20 | EUROBANK | ||||
| 16 | Cash | Withraw cash | Withdraw - ALPHA | c | 200 | 200 | 0 | (799.90) | (799.90) | 19,427.20 | ALPHA | ||||
| 17 | Web Banking | Professional chamber payment | Professional Chamber - EUROBANK | c | 25 | 25 | 0 | (4,297.90) | (4,297.90) | 19,402.20 | EUROBANK | ||||
| 18 | Web Banking | Electricity bill payment | Electricity - EUROBANK | c | 420.5 | 420.5 | 0 | (4,718.40) | (4,718.40) | 18,981.70 | EUROBANK | ||||
Sheet1 | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G2:G18 | G2 | =IF(OR(D2="c",D2="R"),E2,0) |
| H2:H18 | H2 | =IF(OR(D2="c",D2="R"),F2,0) |
| I2:I18 | I2 | =SUMIF(A$1:A2,"="&A2,F$1:F2)-SUMIF(A$1:A2,"="&A2,E$1:E2) |
| J2:J18 | J2 | =SUMIF(A$1:A2,"="&A2,H$1:H2)-SUMIF(A$1:A2,"="&A2,G$1:G2) |
| K2:K18 | K2 | =IF(ISERROR(OFFSET(K2,-1,0,1)+F2-E20),F2-E2,OFFSET(K2,-1,0,1)+F2-E2) |
| L2:L18 | L2 | =RIGHT(C2,LEN(C2)-SEARCH("-",C2)) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| C2:D18 | Expression | =AND(NOT(ISBLANK(C2));ISERROR(MATCH(C2;categories;0))) | text | YES |
| C2:D18 | Expression | =OR(C2="[Balance]";C2="[Transfer]";ISBLANK(C2)) | text | YES |
| C2:D18 | Expression | =OR(ISERROR(MATCH(C2;yearlyA;0));ISERROR(MATCH(C2;monthlyA;0))) | text | YES |
