I am after some guidance on how to best finalise a mechanism to to update a master spreadsheet from a subset spreadsheet. Following is an overview of what I have done.
1. I have a master spreadsheet where orders are entered.
2.. With the help of an EE expert, I have worked out how to copy a small subset of the data to a second spreadsheet that can then be updated (date handled, photo URL, etc) using an AppSheet form.
3. I have used a series of Index/Match lookups to interrogate the subset spreadsheet and display the updated data on the master sheet
However, from time to time, I need to clean out the subset spreadsheet without losing the data. The only way I can think of is to write some VBA that will read the lookup values in the master sheet and paste them back as fixed text on the master sheet before I clean out the subset. I am quite capable of writing the code but wondered if there is a better way?
To make this work I need to save the subset spreadsheet in a completely different workbook in Google Drive (or another cloud synced folder) so that it can be edited by the AppSheet application, That means that there will be times when the master spreadsheet will try to READ data in the subset spreadsheet at much the same time as AppSheet tries to EDIT data in the same subset spreadsheet. Does anyone know if this will cause lock or save conflicts, or is it relatively safe because the lookup is only READing the data, not trying to EDIT it?