LOOKUP / MATCH - Data Verification Sheet

[dinkss]

Hi All,

I'm trying to get double verification worksheet created but don't know how to start.
Basically there are 3 tabs:
1. DATA_EXTRACT
2. PERFORMER
3. VERIFIER

DATA_EXTRACT tab will be hidden with all information about products etc and users will not be able to see it.

My question is: How can I do a lookup / match formula in two tabs (PERFORMER & VERIFIER), where PERFORMER tab and VERIFIER tab will have data entered by user to see if it is matching data in hidden sheet (DATA_EXTRACT). If data will be entered incorrectly - will not match what's in DATA_EXTRACT tab, user should get a message - warning that DATA ENTERED IS INCORRECT.

Can you help me build a formula / macro with something like that?

 

[Joe4]

Can you show us some examples of what the data looks like, and what they will be entering in?

 

[dinkss]

Yes, this is an example, it will be probably 1300 fields

 

[dinkss]

DATA_EXTRACT tab will have all information hidden, and PERFORMER & VERIFIER tab, will have only headers - see row 1.

People will have to enter exactly same data, same match what's in DATA-EXTRACT in order to be correct. If they will not enter information correctly, e.g. instead of 1.004 in column Q they will put 1.003, excel should stop them and display error etc.

 

[Joe4]

Are all the sheets matched up EXACTLY, so they have all of the same columns and headers, and in the exact same order?
And will they have all the exact same rows, and in exactly the same order?

So, whatever they enter into cell Q5 on the Performer sheet should exacly match cell Q5 on the "DATA_EXTRACT" sheet?
Or might the rows be in different orders, and we need to match up on the value in column A ("Material")?

 

[dinkss]

Are all the sheets matched up EXACTLY, so they have all of the same columns and headers, and in the exact same order?
And will they have all the exact same rows, and in exactly the same order?

So, whatever they enter into cell Q5 on the Performer sheet should exacly match cell Q5 on the "DATA_EXTRACT" sheet?
Or might the rows be in different orders, and we need to match up on the value in column A ("Material")?

All sheets will have exactly same columns and headers and exact same order. All will have exact same rows and exactly same order for people to enter the data.

 

[Joe4]

OK, if every cell entry must match exactly, try this:
1. Right-click on the "PERFORMER" sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim ad As String
    
'   Check to see if entry matches DATA_EXTRACT sheet
    For Each cell In Target
'       Get cell address
        ad = cell.Address(0, 0)
'       Compare value to value from same cell on DATA_EXTRACT sheet
'       If not same, clear the value and return message
        If cell.Value <> Sheets("DATA_EXTRACT").Range(ad).Value Then
            Application.EnableEvents = False
            cell.ClearContents
            Application.EnableEvents = True
            MsgBox "Entry in cell " & ad & " has been removed!", vbOKOnly, "DATA ENTERED IS INCORRECT"
        End If
    Next cell
    
End Sub

2. Go to the "VERIFIER" sheet, and repeat step 1 for this sheet.

Now, whenever you make an entry into any cell on the PERFORMER and VERIFIER sheets, it will check it against the DATA_EXTRACT sheet, and remove it if the entry is different, and alert you.

 

[dinkss]

OK, if every cell entry must match exactly, try this:
1. Right-click on the "PERFORMER" sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim ad As String
   
'   Check to see if entry matches DATA_EXTRACT sheet
    For Each cell In Target
'       Get cell address
        ad = cell.Address(0, 0)
'       Compare value to value from same cell on DATA_EXTRACT sheet
'       If not same, clear the value and return message
        If cell.Value <> Sheets("DATA_EXTRACT").Range(ad).Value Then
            Application.EnableEvents = False
            cell.ClearContents
            Application.EnableEvents = True
            MsgBox "Entry in cell " & ad & " has been removed!", vbOKOnly, "DATA ENTERED IS INCORRECT"
        End If
    Next cell
   
End Sub

2. Go to the "VERIFIER" sheet, and repeat step 1 for this sheet.

Now, whenever you make an entry into any cell on the PERFORMER and VERIFIER sheets, it will check it against the DATA_EXTRACT sheet, and remove it if the entry is different, and alert you.

Perfect, I will try that, thank you very much

 

[dinkss]

It w

Perfect, I will try that, thank you very much

It works 100%! Thank you so much! You are a STAR!!!

 

[Joe4]

You are welcome.
Glad I was able to help!