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?
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

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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.