Excel Limitations?

[SpiderMoon]

So the more I have been brainstorming a project I am currently working on, with a projected rate of growth, I am starting to wonder about the limitations of building this project in Excel/VBA...

Ran into a prime example today during a mini meeting. For now, this project will only be dealing with about 500 accounts, but if we take control of the entire portfolio, that will expand to 5000 accounts. How we are dealing with keeping track of notes on the accounts now is dumping the account number and the notes into a seperate spreadsheet. I remember reading some place that Excel only handles 65k rows, and at 5000 accounts, that would only allow for 13 lines each roughly. Not very much space...

Then I was thinking about creating a sheet for each account to house the information on. I am not sure if this is a viable option, or if the load time for the program would be huge having to open 5k sheets if that is even possible. Is it?

This whole project has been a learning experience for me, as all the data is coming from some place else in the workbook instead of from a different system etc (which for some reason, to me is easier to understand).

Anywho, this is more a post wondering if there are any pitfalls I should be expecting, whether I should start looking into building this another way, by a different means, etc etc... Thoughts anyone?

 

[shades]

Depends on what else you want to do with this.

is it possible to group some of the accounts by geography, account manager, division, etc?

If so, you put the Northeast in one worksheet, Southeast in another, etc.. Even dividing by states/countries would make it more manageable.

The limit on the number of worksheets is based on the memory of your computer.

 

[Purple Youko]

I think I would be tempted to store the actual data for each account as a separate file then just pull in each account into the Excel sheet as required.
The Excel workbook itself could then just serve as the interface to display individual account details and select files to save/load.

 

[SpiderMoon]

Oooohhh....

Would that get messy? A file for each account?

Keeping some sort of notation is the big issue really, we already have idea's how to break apart the accounts by client or something similar inside the spreadsheet... perhaps that is where we should make the break?

What about load times? would that slow down the interface opening and closing stuff to get the info compiled?

 

[PaddyD]

Spider,

Previous advise notwithstanding, never, ever go down the "one sheet / file for each account" sort of route - it's only going to give you headaches when you try to aggregate / summarise the data.

You'd be best off searching the web for a few tips about relational database design and normalisation (first normal form) - you might find that you're better off with a single table for the accounts, another table with all the events, and queries / further tables that relate the accounts to the events at the level of aggregation you require. with the source data in access, it can easily be brought into excel for the summary analysis.

 

[shades]

Yeap, after further reflecting on this, I agree with Paddy to go to a database. That's why they are made!