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.
Who is Participating?

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

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.

Martin LissOlder than dirtCommented:
Here is code that will generate a unique key.

Public Function UniqueKey() As String
'Purpose: Generate a unique key. Actually there is a one in
'         10 million chance of the key *not* being unique, but the error
'         handling code in the calling Sub takes care of that.
'Inputs : None
'Outputs: The key for the Treeview

UniqueKey = "K" & 1 + Int(Rnd() * 10000000)

End Function

Sub Test()


On Error GoTo ErrorRoutine
' Do something with the key. This example will of course
' never cause an error and is just here for illustration.
MsgBox UniqueKey


    ' You should test with some known data that the 35602 error number is correct for your situation
    If Err.Number = 35602 Then
        ' Duplicate key, get a different one
    End If

End Sub

Open in new window


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
Martin LissOlder than dirtCommented:
Here is code to transfer filtered data. to anther sheet
Sub MakeArray()
Dim arrVisible As Variant
ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Sheet2").Range("A1")
arrVisible = Sheets("Sheet2").UsedRange.Value
End Sub

Open in new window

Martin LissOlder than dirtCommented:
If the above two work for you I suggest asking separate question or questions for the other two.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rob4077Author Commented:
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!!!
Martin LissOlder than dirtCommented:
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
Rob4077Author Commented:
Thanks for the extra tips. I will definitely look at those articles. Appreciate your help very much
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.