Suggestion to transfer data between spreadsheets

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?
LVL 1
Rob4077Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MurpheyApplication ConsultantCommented:
"I am quite capable of writing the code but wondered if there is a better way?"

This depends strongly on the main reason why you have "a master spreadsheet where orders are entered"
0
Rob4077Author Commented:
Hi Theo,

The main reason is size. I need to make the data available to a mobile device and I will be using AppSheets for that. It works adequately for the job I need to do but it would be much more efficient if it only had to download a subset of the data instead of the full sheet. So I thought it would be best if I kept all the data in a master sheet and only transferred the relevant data to the subsheet. As the subsheet is updated the information needs to be visible on the master and eventually I should clear out the extra data from the subsheet and just leave it in the master.

It seems to me, based on the lack of responses, that I am really asking something that is not ideal anyway and I should find another solution. Is that what you're suggesting?
0
MurpheyApplication ConsultantCommented:
Hi Rob,

Yes, this is not ideal I assume the orders are available in a database (of some brand) so manual interference is never a good solution.
It would be better to get uptodate info on your mobile device(s) by using a Webservice or html5 page that can use an ODBC or JDBC connection.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob4077Author Commented:
Thanks for your confirmation.  I need to explore other options.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.