I have an Excel sheet which contains unique IDs, and another sheet which contains entries which reference by value (via Data Validation - List) those IDs from the first sheet.

I have enabled Data Validation in the 2nd sheet, which successfully prevents values from being entered in the ID column if they do not exist in the 1st sheet.

However, there is a flaw: Excel will still allow an invalid value to be introduced into the ID column in the 2nd sheet, by having the ID value removed or changed in the 1st sheet after it is entered into the 2nd sheet.

How can I force Excel to disallow changing or removing the ID value in the 1st sheet as long as a corresponding ID value exists in the validated column in the 2nd sheet?

  • You could protect the first sheet thus disallowing any changes to it – cybernetic.nomad Sep 17 '18 at 19:58
  • That goes too far. I need to make a heavy amount of updates to both sheets, and I want to be protected from accidentally deleting a value that's required in the other sheet. – Alex R Sep 17 '18 at 20:19
  • 1
    you would probably need a wee bit of VBA code to monitor the lists. Prior to changing a cell in the desiginated range, it can check the other range to see if it is there. depending on the results you can allow the value to change or prevent the intended action from executing. Unfortunately I am an extremely weak VBA programer. If you google it you might find some sample code. You can also ask on stackoverflow for help editing your code when you run into problems with it. And you may get lucky and someone will post some code as an answer here. – Forward Ed Sep 17 '18 at 20:45
  • 1
    Is having a flag telling you a value in Sheet 1 is in use enough for you? If yes, a helper row (or conditional formating) with a COUNTIF would do the job. – cybernetic.nomad Sep 18 '18 at 0:40
  • @AlexR, I'm sure that you are using Data Range from Sheet1 having IDs to validate as Source in Sheet 2. In that case if you lock that Data Range in Sheet1 will help you to do what you want to do !! – Rajesh S Sep 18 '18 at 5:27

As @ForwardEd pointed out, this requires some VBA. The following code will fire when something is changed in your list of unique IDs and check if the old value existed in the other list (your 2nd sheet). If it exists, the change will be undone.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vOld As Variant
    Dim vNew As Variant

    If Not Intersect([ProductListItemID], Target) Is Nothing Then
        vNew = Target.Value
        Application.EnableEvents = False
        vOld = Target.Value
        If WorksheetFunction.CountIf([OrdersItemID], Target) > 0 Then
            MsgBox "Change disallowed"
            Target.Value = vNew
            'MsgBox "Change OK"
        End If
        Application.EnableEvents = True
    End If
End Sub

This should be in the code for Sheet1. I'm assuming two named ranges:

ProductListItemID (List of items on Sheet1 to be protected)
OrdersItemID (List of ItemID's in 2nd sheet)

I've included both ranges on one sheet for convenience: worksheet sample

Explanation: When a change is made in Sheet1 the macro checks if it's within ProductListItemID range. If so, it grabs the changed value (vNew), then does an undo and grabs the previous value (vOld). It then checks if the vOld value existed in the OrdersItemID range. If it was, the cell is left at the old value, otherwise, the new value is restored.

  • It's nicely working Code but has little shortcoming, if I enter new Id in Sheet1 and add the same Id in Shee2, then after this code is preventing the newly enter Id to be modified or deleted in Sheet1. But allows me to Duplicate the Newly entered ID in Sheet1. I think technically it's should never happen cozz Sheet 1 carries unique list. – Rajesh S Sep 18 '18 at 6:04
  • The Undo method you are using is a little bit of an anti-pattern. This means you are undoing and then redoing activities when not necessary. I have just reviewed this anti-pattern in use where I think (I cannot confirm) that it was corrupting the worksheet. – AJD Sep 18 '18 at 6:10

Here's a non-VBA alternative using Conditional Formatting. It does rely on the user knowing what to do, but if you make it obvious enough that there's an error, you should be okay (depending on your users).

1) Add formula to detect errors. In Sheet1, cell B2 enter: =IF(OR(MAX(COUNTIF(ProductListItemId,ProductListItemId))>1,MIN(COUNTIF(ProductListItemId,OrdersItemID))=0),"ERROR","ok") This is an array formula, so you must hit Ctrl-Shift-Enter instead of just Enter. The formula will be surrounded by {}.

2) Conditional Formatting: conditional formatting If you apply the conditional formatting to the entire page or a large enough range, it should be enough to stop the user in their tracks. You could add instructions to 'undo' the changes and try again.

I should point out that the formula in B2 also checks for duplicates in your Product List (items on Sheet 1).


enter image description here


A simple solution is to use the Worksheet_SelectionChange event. I am going to be a little bit cheeky here and use the image that Rob Gale prepared. I have reproduced this image in this answer in case anything happens to Rob's answer.

What the code below should do is lock the cells if a corresponding Item ID is found in Sheet 2. And then, next time a cell is selected it should unlock the cells for future edits.

enter image description here

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim tRangeToProtect as Range
    Set tRangeToProtect = Intersect([ProductListItemID], Target)

    If Not tRangeToProtect Is Nothing Then
        Dim tCell as Range
        Dim tItemFound as Boolean
        tItemFound = False

        For Each tCell in tRangeToProtect  ' "Target" can be multiple cells. Must always hand this.
           tItemFound = tItemFound OR WorksheetFunction.CountIf([OrdersItemID],tCell) > 0
        Next tCell
        if tItemFound Then 
            tRangetoProtect.Locked = True
            Me.Protect UserInterfaceOnly = True
            ' Use this in conjunction with worksheet.Protect UserInterfaceOnly := True
        [ProductList].Locked = False ' Open this up for future checks and editing - remove any existing locks
    End If
End Sub

This is a rough solution only - could be refined based on your business context. Also, I have not tested this, so your mileage may vary.

  • I think this is a nice VBA solution. I avoided using Worksheet_SelectionChange because I've had problems in the past where the user pasted multiple cells. Because of hte fact that you're locking the page, your solution seems to avoid that problem. One minor correction: Me.Protect UserInterfaceOnly = True should be Me.Protect UserInterfaceOnly := True – Rob Gale Sep 18 '18 at 14:51
  • @RobGale: Thanks, fixed the ":". Yes, a drag and drop could be a problem but I haven't done a full test. The code I have been reworking in my normal life uses a global variable to track cut/copy mode, a _Change event to check for validation errors (and then undoes) and a_SelectionChange to prevent cut actions on protected sheets only (by cancelling the cut action, not undo). – AJD Sep 18 '18 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.