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

advice in how to structure workbook/database ?

M

maku

New Member
Hi...I'm new to the site, but have enjoyed reading through the forums and trying to some of the problems and examples on my own. I would classify myself as a beginner/intermediate excel user. The next big step I'm working on is trying to learn array formulas.


Anyways, here's my question to you experts. I'm setting up a new assessment "dashboard" or rating sheet for properties that I oversee at my company. I have 50+ properties and have to enter financial data every month, quarter, and annual. So each property will have 12 monthly entries, 4 quarterly entries, and 1 annual entry. Each property also has to be financially monitored for minimum of 15 years. So there will be a minimum of 255 entries per property.


I've successfully entered a summary sheet that pulls from fields off the "master consolidated sheet". My concern is with 50+ properties and growing, that the file will become too large and not work quickly enough, since it is likely to get 10k-15k lines long in the coming years.


Here's my questions: 1) How many lines can excel handle quickly? 2) What would be the best way to setup the architecture of the system I am creating? 3) This is my dream setup, is it possible?


Dream setup: 1) create a primary workbook that has my summary page of the 50 properties. In this primary workbook, create a worksheet that has a "database" entry form where I can type in the key financial data I want to post to each property. (ie- which month/quarter/year it is for, Monthly revenue, monthly expenses, sales, etc.) 2) Click "POST" on that entry and have that data goto a new excel workbook with the title of that individual property that the data was for and post to a new line there. 3) Then on the summary page on the primary workbook, pull the data from each of the 50+ individual excel workbooks of each individual property name.


My thinking why this might be good is that after 15 years when I'm no longer required to monitor the financial data or if the property is sold, I can just delete that workbook and it would keep the data off the summary page.


Ok..that was very long winded. I look forward to any suggestions I get to setting up the best architecture for this.


Thank you in advance,

-Mark
 
Hi Mark ,


First off , I think you should not have any problem.


The data for each property - how many columns of data are there ?


The quarterly and annual data - are they also entered data , and in no way dependent on the monthly data ?


I think having a workbook for each property is OK , but retrieving data from external files is more time-consuming than retrieving it from one workbook ; why not have all your data together in one workbook , with each property having its own worksheet. Makes it easier to keep backups , if nothing else.


A basic point in structuring a database is that you avoid storing redundant information ; thus the static information of each property should be separated from the repetitive information such as the monthly , quarterly and annual data.


Once the data is available , what kind of reports will be required ? Is it only the one dashboard ?


Narayan
 
@Narayan..that is a great idea to just make each property a worksheet. So excel's file size limit won't be compromised with this much data? I haven't completed it 100% yet, but approximately 10 columns of data, some of which are formulas that calculate KPI's from my entered data. The quarterly and annual data can be entered independently, but I'll probably just formula for the row with an "=if" the data column is a quarter to add up the previous three months. If that data is close enough to the financial statement I receive from the property then that will save me the step of entering the data again. I am making the static parts of each property's data (address, property management company, contact info, etc) so it won't have to be entered more than once...so I think I'm on the right track there. Besides my one page master summary dashboard, I am creating a 1 page "report card" for each property that will be look a lot like a dashboard with a final grade/score given to that property. It will have some graphs that trend the quarter vs previous quarter and year. My plan is to have a similar system with a drop down object like I currently do with my master summary page that selects quarter and year, but I will add property to that, then the 1 page "report card" will populate the data from that property's worksheet in this workbook.


So if I make an individual worksheet for each property (that's 50+ worksheets), should I just then go to that worksheet to post the monthly data on a new row every entry? or is it possible to have a master input page and input the data and have it post it to the appropriate property's worksheet? i guess I don't know how to make data "post" so it may not be the best thing at this point for me to try. It would just be a lot cleaner and easier to goto one place to input data, rather than scroll through the 50+ worksheets. I guess I could make a "menu" with hyperlinks and then that could get me there quickly. just an idea.


thanks for any other ideas and input. I really appreciate it.
 
Hi Mark ,


I think you should go ahead with whichever you are comfortable with ; if the data entry is to be done only by you , you might still want to have a data entry form for convenience ; but if the data entry will be done by others , then it will ensure there is some standardization , and errors can be caught and possibly rectified.


Using a form will involve VBA , since transferring the data from either the form or a master data entry page to the respective property worksheet cannot be done using formulae.


Scrolling through several worksheets is not necessary ; you have the 4 arrows in the bottom left hand corner of Excel ; right-clicking on any of them brings up a pick list , which will show all the worksheets in your workbook. Navigating using this is a breeze.


I think you should prepare the entire framework with 3 or 4 properties ; run this data through the various reports / graphs / ... and once everything is fine , start entering data for the remaining properties.


Narayan
 
Hi, maku!


Let me give an approach that's frequently useful when designing cuasi-databases in Excel workbooks.

- I try to identify objects (nouns, substantives: tables) that represent physical or ideal things, but always things

- Then isolate each object within a worksheet, with each of it properties (attributes, adjectives) as columns

- State which properties uniquely identifies each record (principal or primary keys)

- State which properties let build a relation with other worksheets (secondary or foreign keys)

- Try to maintain the data stored in those worksheets at closest as possible with F. Codd's 3rd. normal form (google it if needed, please)


After all this data design, you'll have only to identify population: how, when, what to control, and surely little pieces of VBA code (snippets) will add security and reduce errors while making all events (methods or operations with data) as automatic as possible, so as to reduce user interaction (use forms for data entry, place validations or business rules within them, and populate worksheets with clean and standardized data.


Hope it helped and not confused you.


Regards!
 
Thank you for your tips..I'm working on restructuring my file...it's challenging...but fun! Thanks for you help!


-Maku
 
Hi, maku!

Glad to help. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top