Rent Spreadsheet Formulas/ Functions Assistance Please

[Joybell]

Hi Everyone,

I am trying to make a rental spreadsheet that moves the week end date each time the rent is paid and if the correct amount is not the outstanding amount is reflected.

My knowledge of excel is limited but I thought I would give it a go, and learn something new. I have spent hours googling but unfortunately due to my limited understanding have found this difficult.

The columns are as follows:
Column B: Rent Due Date
Column C: Rent Start Date (2/2/2021)
Column D: Rent End Date (9/2/2021)
Column E: Number of Days (7 days or 1 week)
Column F: Rent Amount ($335 per week)
Column G: Amount Paid
Column H: Arrears

Do I need Column I: Rent Paid Date?

Any hints, tips or if your feeling generous an actual formula/ function would be greatly appreciated.

Thanks in advance.

[Pepe Le Mokko]

Administrative note

Welcome to the forum

in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

Please take a moment to read it and attach a sheet accordingly.

Thanks you for helping us help you

[Joybell]

Hi Everyone,

I am trying to make a rental spreadsheet that moves the week end date each time the rent is paid and if the correct amount is not the outstanding amount is reflected.

My knowledge of excel is limited but I thought I would give it a go, and learn something new. I have spent hours googling but unfortunately due to my limited understanding have found this difficult.

The columns are as follows:
Column B: Rent Due Date
Column C: Rent Start Date (2/2/2021)
Column D: Rent End Date (9/2/2021)
Column E: Number of Days (7 days or 1 week)
Column F: Rent Amount ($335 per week)
Column G: Amount Paid
Column H: Arrears

Do I need Column I: Rent Paid Date?

Any hints, tips or if your feeling generous an actual formula/ function would be greatly appreciated.

Thanks in advance.

[Joybell]

Hi Pepe,

I apologize for not reading the yellow banner. Thank you for the feedback.

[Glenn Kennedy]

Hi. That layout might complicate things a bit.

I'd suggest the layout in the file. Keep one sheet to record payments and another to act as a summary sheet. Happy to explain anything. No point in doing so now, in case you don't like it!!

[Joybell]

Hi Glenn,
Thanks for your help.

I like the concept of the spreadsheet, I had not considered this type of format however, your way will probably work better than mine.

Please explain away!

[Glenn Kennedy]

The formula that is most likely to cause you to stare at it is:

=IF([@Property]="","",LOOKUP(2,1/(Payments!$A$2:$A$1000=[@Property]),Payments!$B$2:$B$1000))

red: If the property number column in theis row is blank, return a blank.

Green: select the rows in Payments corresponds to the property number in the row. This will return an array of either TRUE or FALSE.

Blue: 1/TRUE = 1 and 1/FALSE # #DIV/0 error. So, now you have an array of 1s and errors.

Purple: Lookup will look at that array for a value of 2. it won't find one, as it contains only 1s and errors. It then defaults and returns the LAST matching value...

Cyan: ... from the results column (the most recent payment date for that property).

If any of the others cause your eyes to pop out... ask and i'll explain them. If not, then, you're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

[Joybell]

Thanks Glenn,

I will give this a try and come back to you if I need assistance.

I really appreciate you taking the time to help me.

[Glenn Kennedy]

You're welcome. Any time.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

[Joybell]

Hi Glenn,

This is probably a stupid question but do the different coloured formulas mean they are separate functions that you placed in separate cells? I'm trying to understand the process of what you have done. I'm liking it though.

[Glenn Kennedy]

All the formulae are already in the sheet that I posted.

[Joybell]

Yes, thank you.

[Joybell]

Hi Glenn,

I'm sorry to be such a novice.

I'm assuming the spreadsheet is for multiple properties givens it says Properties in the first column, that great as I have two rentals. However I'm still slightly confused where I enter the weekly rent for the tenants as there is only one row per property. I worked out the second sheet is linked to the first but I don't get how it all works together. I did get the rents to have nil arrears using the second sheet but where do I put the following weeks rent?

[Glenn Kennedy]

I had to call column A something (on both sheets)... so I called it Proprty. Now renamed Tenant. Enter the regular payments on the second sheet (Payments) starting at the next available row and just keep on adding them on as they are paid.

Everything should update automatically on the summary sheet.

[Joybell]

You are a champ, thank you.

I swear this is the last question, how do I protect the formatted cells when inserting new lines

[Glenn Kennedy]

hahaha. Ummmm. why would you want to insert new lines?

Just start typing on the next row. The formula will take all the data in the payments sheet and the structured table will automatically expand and copy don the formulae as you type.

[Joybell]

Got it, thank you. I did say I was a novice.

[Glenn Kennedy]

Every journey begins with the first step!!

Keep asking questions... that's the way to learn.