Using Excel as a Database
Posted by Jim on December 27, 2001 5:24 AM
Not ideal I know - and I guess that's what Access is designed for but here is the situation:
I have a list of unique customer numbers in Sheet 1 column A these I allocate to a debt grouping from a template sheet (i.e. liquidation, write off, active, ceased etc). I then import a report into a different sheet and VLookup the customer allocation. The problem is there are #N/As where customers are new or not listed in the sheet1 list. I need to get the user to run a macro (or another way) to cycle through all the customers with errors and preferably to select an allocation from the list in template sheet. Then copy the new customer and the allocation to the first listing of all customers so as the lookup will update.
Does anybody have a similar spreadsheet set up that I could use as a crib sheet? Failing that I am struggling to get the script right for the dialog allocation listing.
My e-mail is crabbsie76@hotmail.com
Thanks in advance.
Jim
Posted by Joe Was on December 27, 2001 7:10 AM
Jim,
Your note is contradictory. You say the problem is the debt sheet is missing client name data, due to their being new clients or clients with debt who are not on on the client ID sheet, Sheet1. Not to fear I know how this happens; You start with an application to do a task, then you add additional functions over time and eventually you get an out of control monster.
The flaw in you system is the root form in a hierarchal data base system or the key matching data in a relational form, should be the Client ID or Name. All functions on a service oriented, single sided database system should be from or with the client info. For a sales or double sided database the two first branches to the system should be the Client info and the Inventory sides.
In any case you need to build a user interface for your system:
A master control and navagation overlay. Here you can link different applications, which you have now, together through user forms or a menu sheet and frames. This way you don't need to build a whole new system to accommodate the expansion and growth of your applications.
Yes you can build good database applications and systems with Excel, you may even get around some of the limitations of Acess and have a faster system under certain conditions.
Re-post what you need. JSW
