How to create a validation rule in Excell will restrict input of the values containing in onether excel sheet

[Tnaro]

Hi, I would appreciate if you can give some ideas to solve the problem below:

I have a column called "TV Model" on sheet 1, on sheet 2 I have a list of 253 TV Models that are repaired by another company. My question is how can I create a rule when I am inserting any model from sheet 2 to have an alert stating that this TV Model is repaired by other company, but allowing me to insert all other TV models which are not restricted by this rule. The TV models contains capital letters and numbers,
I have tried to create the rule with Data Validation and is generating the Alert for every input.

 

[galbert]

Hi,

You could add a custom data validation to your sheet. Assuming you name the range that contains the TV models as TV_list and your entry range starts at A2, you could enter the following formula to the Custom data validation rule:

=ISERROR(INDEX(TV_list,(MATCH(A2,TV_list,0))))=TRUE

The formula checks if the INDEX/MATCH functions return an error (meaning the entered value is not found in the list) and if that's the case, it allows the user to enter it. If the result is not an error (but the actual TV model which is part of the list), the data validation displays the error message.

You need to select the whole range where you are planning to enter TV models and apply the data validation to it.

Then you can configure the error message, as usual, using the Error Alert tab of the data validation dialogue box.

Hope this helps.