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.