Landlord spreadsheet

frippery

New member
Joined
Jul 17, 2013
Messages
3
Reaction score
0
Points
0
I have been trying to create a spreadsheet that will automate, as much as possible, the things which I normally look for when dealing with my tenants. I am a landlord and I need to keep track of tenants payments and when they move out, other things as well, but these are the most important things. So I decided to make it so that the name of the person and the date they move out would highlight in red, based one existing data.

In the case of payment, I wanted the F column to turn red on the day displayed in the field; so if is "14" then on the 14th of the present month it would turn red. While I can get this to work it turned out that I could not switch the red off,
there was no operator for it, so I decided it needed to be linked to a table where I could dictate when/whether payment was received or not, or even late.

I have a table beneath the smaller top one showing the months of the year and the tenants (21B an onwards), when it's time for someone to pay I can select from a drop down menu if they paid or not, if the cell contains paid then the other, smaller table, will not show red, according to the date in the F field e.g. F8.

e.g. If F8, for Samuel, went red because it was time for him to pay the rent and it was the 16th of the month then I would check off in I22 (being July right now) either "Yes" or "No", or "Late" and that would, in the case of "Yes", make field F8 return back to white.

I was thinking that the property business is never that black and white and that some people pay late, so I would like to have a third option labelled "late", which in itself is a "Yes" but of course, not on time. So, for example, they might pay a week late and so I could leave the field blank, or use No, then when they do pay, use the "late" option and it would be on their record as it were.

I did toy with the idea of having the date print
out when they paid, but I might not get around to editing the accounts for a few days after, a lot of information is kept in my head and I remember a lot of things, and it could make the record inaccurate. The other option is to manually enter it and I think that defeats the purpose of the spreadsheet - which is to make things easier. I think, however, that the "late" option is enough, perhaps I could have a percentage field next to their name to show how reliable they are: calculated on their stay time and whether they paid on time or not, or were late, that could be useful.

So there would be two tables: the first is the smaller one which would be the go to place for quick info like when they moved in or are due to leave and if they're rent is due, the red highlight really helping. While the larger table below contains the detailed information like month by month payments.

View attachment yihong FIN (1).xlsx

Can anyone help thanks.

Thanks
 
First thing that I would suggest is not to use text names for months, but use a data & format it to mmmm. So instead of "January" use 01/01/2013 a format as "mmmm" will display as January
 
First thing that I would suggest is not to use text names for months, but use a data & format it to mmmm. So instead of "January" use 01/01/2013 a format as "mmmm" will display as January

I just want to get the red CF to recognise if the persons paid the rent on time, from the drop down cell in the other table.

And I don't know how.
 
frippery, there is nothing wrong with cross posting as long as you do it right, every site (pretty much (including this one)) asks that you supply urls to ALL your crossoposts, it's explained elequently here http://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters please take the time to read it, it will help you at every forum you visit.

I am now cleaning this thread up of all posts except this one and yours and Roy's initial posts.
 
Thanks flying penguin, but you know I've got this feeling that nobody's going to help me on this forum.

All I think I need (but it's never that simple) to get the CF affected filed, which is red, to stop being so when one of two or three options in a drop down menu is used.

Can it even be done?!
 
Back
Top