Automating rental property management excel spreadsheets.

[Mono]

Hey guys, I have been asked to help a friend with her excel sheets for her rental property management company. Essentially she has found that she now spends far too much time manually copy and pasting information from bank statements into various different summary sheets and would like some help automating it.
Currently the process is every few days she downloads her bank statement as an excel file, she then copies the debits into one spreed sheet and the credits into another. Then when there is a rental payment she has to then copy that into the specific spreadsheet for that property with just the rental payments and the dates, then onto a summary spreadsheet for that property that has all the total rent and costs (rates maintenance etc.) for that month for that property.
What I am hoping to be able to do is create some drop down lists or check boxes so that when she downloads the the bank statement, she can just select what property it is relevant for, and what type of payment, and then excel will automatically write the relevant bits of information in all the relevant spreadsheets based on the date of the payment, and the options chosen.
Is this possible using excel? Any advice on how to get started and how much programing or macros I will need to use? If someone has an example of using lists to sort information into different spreadsheets that would be really helpful.
Thanks
Angus.

[jmac1947]

Hi Angus, and welcome to the forums

So, it is all possible in Excel and not all that "tricky" in terms of very clever vba to achieve the result.

I am not sure about your excel level of skill or VBA level of skill but I would start by gathering / creating a set of sample spreadsheets (obviously with sanitised data to protect confidential financial information) that covers all the major spreadsheets your friend uses as well as a couple of examples of the property spreadsheets and a very clear statement of the expected results from a sample bank statement download. (this will be particularly important as most banks have different codes / formats and while I am also in OZ and, depending on the actual bank, can generate my own downloads, forum contributors from other places will not have that luxury.

So, some good old fashioned "business analysis" tasks before you even think of picking up your "coding pencil"

Once you have that clear idea of where to go then it will be relatively simple to map out the coding routines you need.

I am happy to assist along the way (i might learn stuff or perhaps find a useful solution for my daughter who has the same problem albeit on a very much smaller scale)

Jmac

[Mono]

I have all the spreadsheets you've mentioned, but as you say its all confidential. I better start making some mock ones. The bank was commonwealth bank so the format is just column for date, column for description, for debit, for credit and finally balance.

[Mono]

So it seems its all possible with VBA, I haven't used that yet but I should be able to learn it pretty quickly. I'll probably work few a few tutorials online and see if I can find a way to do what I'm aiming for, I'll post back if I get into trouble.

[jmac1947]

No Problems, and good luck.

If you get stuck or want a couple of examples then feel free to email me (jmac1947@bigpond.com) or come back to the forum with a new question (s)

In the meantime this thread seems like it is "solved" so please use the thread tools (top right of your first posting message) to mark the thread solved