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
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