Validating the Sheet1 column value with sheet 2 values

Question

In my excel Sheet1 i have Vendor_Id column. In Sheet2 i have List of Vendor_Id values ( like VNDR001,VNDR002,VNDR003.....,VNDR020).

When user enter the value in Sheet1 Vendor_Id column i need to check that value is present in Sheet2 Vendor_id Column or not.

If the user entered value is not present in sheet2 then i have to display a message "Vendor is is not present".

You can do this with data validation. Set data validation for (Sheet1) Vendor_Id column by highlighting the column. On the 'Ribbon', locate the 'Data' tab, then click on 'Data validation', Change the Validation criteria to 'List' and set the source as the (Sheet2) Vendor_id Column. You can then change the message in the Error Alert Tab. — 5202456
it would be easier to understand your question if you add screenshots or data samples — Scinana
Please close it as he is expecting people to give him entire VB code — Kin Siang
He question can be solved by excel formula easily and he is not that skillful to design a fire event in VBA — Kin Siang

Thomas Lilleng Thomas Lilleng · Accepted Answer · 2021-05-27T15:34:14

Data validation is designed to do just this for you.

Select the cells you want to be validated, and hit the "Data Validation" button. Choose list under 'Allow' and refer to the list that the previously selected cells are to be validation against as source. You can choose whether to have a drop-down list in the cells. In most cases I do, but in your case right here I probably wouldn't.

As for the error msg, choose the tab that says Error Alert, and fill in what you want it to say.

I hope this works for you. If this or any other answer solves your problem - please consider as accepted answer.

Validating the Sheet1 column value with sheet 2 values

2 Answers