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

Tnaro

New Member
Joined
Oct 7, 2020
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

galbert

New Member
Joined
Oct 1, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Formula:
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,122
Messages
5,628,819
Members
416,342
Latest member
BlueDevil12

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top