I am looking for some strategy help in using Excel to update a common workbook that would also be available for searches and recall.
We have a Contact spreadsheet (currently in Open Office Calc) used for capturing orders from customers and creating Confirmation sheets (pdf) that get emailed to customers. When the order is created the data is stored in a separate sheet (Ärchive) in a flat file format before creating the Confirmation sheet. The user can create a new order, search for and recall a row to update it, clone it to create a new order, or just reprint the Confirmation.
There are now 2 users that have separate workbooks with their own copy of the full data, with the accompanying reconciliation and data problems.
I'd like to rebuild it in Excel, and separate the Archive sheet into a separate workbook replicated over Dropbox so the users could share the data, only accessing it while they searched or updated the data. The likelihood of concurrent update is very low as long as the shared workbook could be opened only to search and update, then closed again to allow Dropbox to replicate it. This is my preferred strategy because it allows the primary user to have full use of the application offline, then sync all the changes when reconnected.
The Archive workbook will be about 4k rows at start, and should grow by about 1k per year, but should never be more than 10-15 Mb. There are about 85 columns in the current flat file arrangement, but I plan to break it up into Contact and Order segments in the new version.
Should I have separate macros stored in the Archive sheet to apply creates and updates to the data, or store them in the user workbook?
If I use an external data connection to search and recall the data from the Archive workbook can it be opened and closed quickly, or would copying the updated sheet into the user workbook for searching make more sense?
Thanks in advance for your help.
Bookmarks