I have worked in excel but this is beyond my knowledge. I have a spreadsheet (someone built for me) that needs to figure the prorated amount of rent due starting the month the tenant moves in and thereafter. It also needs to be able to automatically charge a late fee of $20.00 if the rent is not paid by the 15th of the month. Right now it's putting the rent due in all the months even before the move in date. Also, I have 8 rows (2-9) that I need the formula to work for when a tenant moves out and a new tenant moves in (not even sure if that can be done). It then updates a sheet called "Dashboard" to show by month how many units are available, how many are rented, total rents to be collected, total rents outstanding, # of units current and # of units in default. Last question, I have over 200 sheets in this workbook, it there a way to update the formulas on all the sheets at the same time? TIA
| Xtra Space Workbook Original File.xlsx | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 1 | Size | Climate Controlled | Occupied | New Lease | Move in Date | Customer's Name | Phone # | Address | City | St | Zip | E-Mail Address | |||||
| 2 | 10 X 10 | Yes | Yes | Yes | 4/21/2020 | ||||||||||||
| 3 | |||||||||||||||||
| 4 | |||||||||||||||||
| 5 | |||||||||||||||||
| 6 | |||||||||||||||||
| 7 | |||||||||||||||||
| 8 | |||||||||||||||||
| 9 | |||||||||||||||||
| 10 | |||||||||||||||||
| 11 | Rental Rate | $98.00 | |||||||||||||||
| 12 | |||||||||||||||||
| 13 | January | Febuary | March | April | May | June | July | August | September | October | November | December | |||||
| 14 | Amount Due | $98.00 | $196.00 | $294.00 | $392.00 | $392.00 | $392.00 | $392.00 | $392.00 | $392.00 | $392.00 | $392.00 | $490.00 | ||||
| 15 | Amount Pd | $98.00 | $98.00 | $98.00 | $98.00 | $98.00 | $98.00 | $98.00 | |||||||||
| 16 | Date Paid | 5 | 7 | 4 | 6 | 10 | 2 | 5 | |||||||||
| 17 | Late Fee Assessed | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | ||||
| 18 | Balance | $98.00 | $196.00 | $294.00 | $294.00 | $294.00 | $294.00 | $294.00 | $294.00 | $294.00 | $294.00 | $392.00 | $490.00 | ||||
| 19 | |||||||||||||||||
| 20 | Current | No | No | No | No | No | No | No | No | No | No | No | No | ||||
226 | |||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C14 | C14 | =C11 |
| D14:N14 | D14 | =C18+$C$11 |
| C17:N17 | C17 | =IF(C16>15,20,0) |
| C18:N18 | C18 | =C14-C15+C17 |
| C20:N20 | C20 | =IF(C18=0,"Yes","No") |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| C20:N20 | Cell Value | ="No" | text | NO |
| C20:N20 | Cell Value | contains ""No"" | text | NO |
| C20 | Cell Value | ="Yes" | text | NO |
| C20:N20 | Cell Value | contains "Yes" | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| A2 | List | =Dashboard!$C$100:$C$105 |
| B2:D2 | List | =Dashboard!$B$100:$B$101 |
