Help developing an Invoicing Workbook

[tropical]

Hello All,

I am very new to this site and pretty much a basic user of Excel. I have a situation where I have several buildings with several tenants with varying costs and expenses that are invoiced for each month. I (somehow) manage all of this info in a bunch of Excel worksheets that have evolved over the years. I am now slowly cleaning up my work and stumbling over many errors.


My approach is to get all of the DATA inputs into several secure worksheets, perform the ANALYSIS and summary in a single worksheet, and then merge the findings into the individual PRESENTATIONS (invoices). Of course, I would also like to maintain a historical record (worksheet).


I see lots of Excel templates out there for invoices, but no info on creating complex invoicing solutions (workbooks). There are so many details to this project that I am counting on it elevating my Excel capabilities. While I continue the data input cleaning, I am wondering if anyone can a) suggest some good specific resources for this project, b) share some conceptual brilliance to the overall project, and 3) suggest the best means of compiling all of the data and pushing it to a single invoice template?


Any help will be greatly appreciated.....


Regards,

tropical

 

[SirJB7]

Hi, tropical!


The scope of a post on this forums perhaps isn't as wide so as to provide you with a practical answer to your so-wide, complex and specific question... question that more than an inquiry or doubt seems to be a summary of specifications for an Excel-based system.


Tried contacting Chandoo? http://chandoo.org/wp/contact/


Regards!

 

[dan_l]

Hi Tropical,


I think you could do this in Excel. I think you should do this in Access.


Off the top of my head:

You've got units, tenants, buildings. You've got different rents paid, unpaid, partially paid. You've got practical usage requirements that span from data entry, reporting/analysis, and the need to run an invoice off.


Don't get me wrong: excel can do all of that. But depending just how deep this thing gets, you may find a more robust solution in Access or oobase.


I'm not trying to get into your stuff here, but just how many 'unique' units are we talking and how much do they vary in the particulars?

 

[tropical]

Hi dan_I,

Thanks for responding. Yes, as you suspected, it is quite complex. I had also considered using Access, but know less about it than I do about Excel. I think I have been cleaning the individual worksheets (tenants, square footage, electric, gas, garbage, sewer, water, common area maintenance, etc.) so that they might someday be migrated into databases.


For now, I am excited by the challenges of actually learning Excel. In general, I'm slowly converting the input to use Excel's DataForm feature, and the outputs using HLOOKUP and VLOOKUP functions. Thoughts?


Thanks again,

Tropical

 

[dan_l]

I get it.


Something like this either ends up being a really complicated spreadsheet or a relatively simple database. I'm practical like that


So at the minimum you'll need:

-A table containing units and their expected rent. Each unit will need a unique ID.

-A table where you'll be adding payments you debits and credits. It would probably best if you associate them with a unique ID.

-If it's actually going to do the invoice for you, you'll need a table for customer details, again with a tie back to your unit ID.

-A form for your output.