CODEWITHSUNDEEP
excelexcel-formulavalidationvba
user3331363
user3331363

Reputation: 345

update cell if data is present in a column

Say there are two Worksheet: Worksheet 1 , Worksheet 2

Worksheet 1 has a Column called 'Number'

Worksheet 2 also also a Column called 'Number'

Now i am entering a number in the 'Number' column of Worksheet 2.

I now need the Number i entered in Column 'Number' of Worksheet 1 to be matched against 'Number' column of worksheet 1.

If there is a match then allow the entry on Worksheet 2 or else throw an error 'Invalid Data'.

Can you guys please solve the same for me :)

Upvotes: 0

Views: 933

Answers (2)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

There is no need to use VBA. You could use Data validation:

Sheet1:

enter image description here

Sheet2:

Select entire column "Numbers" in sheet2. go to Data->Data validation. Select Custom and enter formula:

=AND(ISNUMBER(MATCH($A1,Sheet1!$A:$A,0)),COUNTIF($A:$A,$A1)<2)

where Sheet1!$A:$A address of column "Numbers" in sheet1. $A:$A - address of column "Numbers" in sheet2.

  • Part ISNUMBER(MATCH($A1,Sheet1!$A:$A,0)) allow to enter values only from sheet1.
  • Part COUNTIF($A:$A,$A1)<2 doesn't allow duplicates

enter image description here

Select "Error Alert" tab and enter error message.

enter image description here

Done!

Upvotes: 2

Jacob Lambert
Jacob Lambert

Reputation: 7679

Something along these lines should work:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim TarColumn as Integer 'stores the column number of the modified cell
    TarColumn = Target.Column 'sets the column number

    If TarColumn = 1 Then 'replace 1 with the column number of your Number Column
        Dim RowCountA as Long 'stores the amount of rows in your worksheets
        Dim RowCountB as Long
        Dim a, b 'will store the numbers from the number columns

        RowCountA = Worksheets("Sheet1").Cells(Rows.Count,1).End(xlUp).Row 'find the last row of data
        RowCountB = Worksheets("Sheet2").Cells(Rows.Count,1).End(xlUp).Row
        a = Worksheets("Sheet1").Cells(1,1).Resize(RowCountA,1) 'copys the numbers into the arrays
        b = Worksheets("Sheet2").Cells(1,1).Resize(RowCountB,1)

        For i = 0 To RowCountA - 1 'checks to see if it is in the first sheet
            If Target.Value = a(i,1) then
                MsgBox("Invalid Data")
                Target.Value = ""
                Exit Sub
            End If
        Next

        For i = 0 to RowCountB - 2 'ensures no duplication in the second sheet
            If Target.Value = b(i,1) then
                MsgBox("Invalid Data")
                Target.Value = ""
                Exit Sub
            End If
        Next
    End If
End Sub

You might need to modify the .cells(1,1) to fit your code depending on headers and location in the sheet.

Upvotes: 1

Related Questions