• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is this possible? Tenant payments management

O

officelurker

New Member
I am new to excel. My boss purchased a building last year with 30 tenants.

I have been doing the bookkeeping by hand but it is getting tedious.

I want to use Excel. Here is what I want:

1) A ledger for each tenant - each tenant would likely get their own worksheet right?
2) A page which shows me at a glance the balance for each tenant (derived from the ledgers)
3) optional - a place where I can easily enter rents that would automatically go into the appropriate ledgers - keeping in mind, NSF checks and some tenants make two or three payments per month.
 
Good day officelurker

Yes this can be done,......but as there are many ways to skin a cat there are as many ways to do this, a separate sheet for each tenant, just make one get your layout as needed, column/rows/formulas/CF/Font/Font size, ect, ect get the first sheet right take time to look and check that the layout is as you want and the copy it as many times as needed then just fill in the tenants name on a sheet AND CHANGE THE SHEET TAB TO THAT NAME this way you know which sheet belongs to which tenants data.

You could hyperlink each sheet in the Index sheet which would of course be the first one.

You could have the totals of each sheet in a separate totals sheet.

Search for templates and alter as required

When you have done this and you have a more defined question, upload your work and let members take it from there.


Luke M your keyboard speed gets faster, no reply's when I started.....:)



.
 
Or, if you want really simple, here's a table and PivotTable report for you. Just input your info into the Table, and then refresh the PivotTable to see how things stand.
 

Attachments

Ok, so I have a draft workbook attached. It has three worksheets for now. Two tenant worksheets (this will go up to 30 as I have 30 tenants) and one summary one.

Questions:

1) In worksheets 'A' and 'B', the balance column formula is not working. What is going on? FYI Some tenant are starting off with a zero balance, other tenants will have a carry over balance figure.

2) The 'summary' worksheet is supposed to show the final balance totals from each tenant. How do I get that working?

This is all for now. I really appreciate all your help.

(i should add that eventually i want to be able to add payments from one worksheet and have each tenant worksheet populated with that payment - instead of entering payments individually)
 

Attachments

Hi ,

In F5 , put in the following formula ( assuming you have Excel 2007 or a later version ) :

=D5-E5+IFERROR(1*F4,0)

Regarding your second question , it will become difficult if you retain your present system of naming the sheet tabs ( A , B ,... ) when you scale it up to 30 tenants. It will be better if you use the same text which you are using in the Summary tab to identify each tenant , as the sheet tab.

Narayan
 
Hi ,

Check your file now ; I have done what I had suggested in my earlier post viz. rename the sheet tabs according to the tenant names in the Summary tab. Note that these have to match exactly ; even one extra space will matter.

Narayan
 

Attachments

Thank you Narayan! That is exactly that I was looking for. When I add another worksheet, I should jut copy down the formula right in the 'summary' worksheet?
 
Hi ,

Yes , provided the sheet tabs have been named the same as the text in column B on the Summary tab.

Do add more data on the individual tenants' worksheets and double-check.

Narayan
 
perfect. thank you so much.

is there any way i can enter payments from one worksheet that will be populated in their respective worksheets for each tenant?
 
Hi ,

There have been several questions on this subject in this forum ; it is possible using either formulae or VBA.

I'll post the links once I find them.

Narayan
 
I will try it out but a few issues might come up. i.e. will i have to enter the rents due for 12 months before hand? will i have to leave open rows for the payments? how will the code determine the where in the ledger to enter the payment (for each month)?

but thanks for the link and your help. appreciated.
 
You must log in or register to reply here.
Back
Top