I sent the first piece over to the client – I didn’t have the ‘gig’ at this point, but after a few minutes discussion and review of the work I did we had a deal. The client was a bright guy, clearly with an aptitude for VBA and Technology. I asked why he didn’t put it in access? The answer was predictable:
- a number of people use this spreadsheet and they dont know access
- management is fussy with what it looks like
Excel vs Access
This all left me thinking about two things: when is it time to switch to MS Access? When you analyze this particular workbook for what I’ll call its Root Function, the answer is it slices and dices data, which sounds like what a relational database does best.
I have a good deal of experience using Excel as the front end with an access database as the “data crunching” engine. Any required format can easily be achieved. The reality is its working and as long as its working no one wants to invest the time and money to improve it.
Heres my thoughts on things to consider if its time to switch to access.
1) Root function of the worksheet is data sorting and tabulation
2) The data is volumous, thousands of rows
3) you need to turn calculation to manual
4) The workbook has lots of instructions and “Dont change this”
5) A preponderance of vlookups, xLookups and nested formulas (complexity)
There is a phenomena out there called “spreadsheet creep”. It started with a small simple spreadsheet and grew into this unwieldy monster. It may have been modified by a few different people. It works well you think, but its the elephant in the room no one dares mention.
Do you have an elephant? Can I help you make it right before the unthinkable happens? The solution could be an Excel Front End (GUI) and an Access Database Backend doing its data crunching magic.
contact me Here