Rob4077
asked on
Suggestion on how to sync data between two spreadsheets
I am after some expert comment and help on whether this approach is reasonable. Following is the entire process flow with areas I need help with highlighted in bold:
1. Orders get manually entered on a macro enabled spreadsheet.
2. Duplicate orders over time are possible and so there is no combination of fields that is guaranteed to be unique. I can't rely on row number because the source data may be re-sorted, Any ideas on how can I create a unique key?
3. Orders are manually allocated to different delivery runs.
4. Those orders need to be transferred into a non-macro enabled spreadsheet (xlsx). Is there a way to "extract" filtered data to another spreadsheet, or do I need to select and copy manually (or with vba)? I remember back to the days of an Excel pre-predecessor that had a "data extract" function. Is there an equivalent in Excel?
5. The AppSheet app will be used to update the data in the non-macro enabled spreadsheet (date and time delivered, URL to photo, delivery notes etc).
6. The updated data needs to be visible in the macro enabled sheet, which I can do with vLookups using a unique key mentioned above. From time to time I need to clean out the non-macro enabled spreadsheet but I need to make sure the vlookuped data remains available.. Should I write some vba to copy calculated values from my vlookups as fixed text. Or is there a better way?
7. Finally, when an Excel vlookup on one workbook interrogates a completely different workbook that is saved to Google Drive and therefore theoretically shareable, does anyone know if the interrogated workbook is locked for editing? The reason I ask is that I don't know how well Excel handles conflicts when one workbook on a desktop is looking up data (using vlookup) at the same time as AppSheet (effectively another user) is trying to edit records in the same spreadsheet at the same time.
1. Orders get manually entered on a macro enabled spreadsheet.
2. Duplicate orders over time are possible and so there is no combination of fields that is guaranteed to be unique. I can't rely on row number because the source data may be re-sorted, Any ideas on how can I create a unique key?
3. Orders are manually allocated to different delivery runs.
4. Those orders need to be transferred into a non-macro enabled spreadsheet (xlsx). Is there a way to "extract" filtered data to another spreadsheet, or do I need to select and copy manually (or with vba)? I remember back to the days of an Excel pre-predecessor that had a "data extract" function. Is there an equivalent in Excel?
5. The AppSheet app will be used to update the data in the non-macro enabled spreadsheet (date and time delivered, URL to photo, delivery notes etc).
6. The updated data needs to be visible in the macro enabled sheet, which I can do with vLookups using a unique key mentioned above. From time to time I need to clean out the non-macro enabled spreadsheet but I need to make sure the vlookuped data remains available.. Should I write some vba to copy calculated values from my vlookups as fixed text. Or is there a better way?
7. Finally, when an Excel vlookup on one workbook interrogates a completely different workbook that is saved to Google Drive and therefore theoretically shareable, does anyone know if the interrogated workbook is locked for editing? The reason I ask is that I don't know how well Excel handles conflicts when one workbook on a desktop is looking up data (using vlookup) at the same time as AppSheet (effectively another user) is trying to edit records in the same spreadsheet at the same time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If the above two work for you I suggest asking separate question or questions for the other two.
ASKER
That's awesome!!!. That's exactly what I needed for questions 1 and 2. I will do as you suggest and raise another question for the other two questions. Thank you very much!!!
You’re welcome and I’m glad I was able to help.
If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.
Marty - Microsoft MVP 2009 to 2017
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.
Marty - Microsoft MVP 2009 to 2017
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
ASKER
Thanks for the extra tips. I will definitely look at those articles. Appreciate your help very much