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.
Rob4077Asked:
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()

Randomize

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

ErrorRoutine:

    ' 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
        Resume
    End If

End Sub

Open in new window

0
 
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

0
 
Martin LissOlder than dirtCommented:
If the above two work for you I suggest asking separate question or questions for the other two.
0
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!!!
0
 
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
0
 
Rob4077Author Commented:
Thanks for the extra tips. I will definitely look at those articles. Appreciate your help very much
0
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.