Conditional Formatting while checking for data validation against data in a Table

[asorifice]

I want to enter data in a cell in one tab of a spreadsheet , check it against values in a table (in another tab in the same spreadsheet), allow the entry either way but use conditional formatting to highlight the data if it is not found in the table. Possibly also adding a warning message about the non matching data

 

[Jeffrey Mahoney]

That can be done. We Need an example of the value and the data table. The warning message isn't possible; not as an automatic thing

 

[asorifice]

That can be done. We Need an example of the value and the data table. The warning message isn't possible; not as an automatic thing

Thanks Jefferey hope this helps

The mapping value would be the Product value
This is the tab where the user would enter data into the product field
They enter a product value and if it matches a value in the table then the other info is mapped in from the table, if it doesnt match (second entry) then no data is mapped and the entry is highlighted

	Product	Vendor	TQ Approved Version(s)
Entry with a match in the source data	ANSYS Icepak	Ansys	2020 R2, 2019 R3, 2021R1, 2020R1, 2021R2
Entry with no match in the table	a;ldkf;lakjf

The data from the source / table tab in the same spreadsheet

Product	Vendor	ToolQuest Version
ANSYS Icepak	Ansys	2020 R2, 2019 R3, 2021R1, 2020R1, 2021R2

Is this the information you are looking for?

 

[Jeffrey Mahoney]

Conditional Formatting Formula. This assumes your product column is Column A and the same for the product list. You may want to turn your product list data into a table and reference the Product column by its Excel TABLE format, or use a dynamic named range
=AND(COUNTIF('Product List'!$A:$A,$A2)=0,$A2<>"")

You can also add a Data Validation for the same range so it will display an error if a value doesn't match. IF you want it to accept the wrong value but only display the error then Choose Information Style Error. Again, an Excel TABLE reference would be better.

 

[asorifice]

Conditional Formatting Formula. This assumes your product column is Column A and the same for the product list. You may want to turn your product list data into a table and reference the Product column by its Excel TABLE format, or use a dynamic named range
=AND(COUNTIF('Product List'!$A:$A,$A2)=0,$A2<>"")

You can also add a Data Validation for the same range so it will display an error if a value doesn't match. IF you want it to accept the wrong value but only display the error then Choose Information Style Error. Again, an Excel TABLE reference would be better.
View attachment 52704
View attachment 52702

Thanks Jeffrey Well we are getting closer I love the idea of a conditional formatting with Style Information. The user can enter invalid data with just a warning and not an error
Now onto the conditional formatting I am still stuck reference error messages below
My user data entries are on Sheet 2 of my workbook the heading is Product List with the user entries starting in A2 and the Table with the reference data is called Product_List and the field name in that table is Product List and that title is in A1 , with the data starting in A2. The tab in the workbook with the Table is Sheet1
I go to Conditional Formatting , select New Rule, Select use a formula and have been trying a few secenarios with these errors
I tried entering the formula into cell A2 on Sheet 2 where the user entries are and as shown below in this first image

 

[Jeffrey Mahoney]

In that formula I used a sheet named called "Product List". You will need to use the sheet name you have for the data list.

 

[asorifice]

In that formula I used a sheet named called "Product List". You will need to use the sheet name you have for the data list.

Thanks Jeffrey
Houston we no longer have a problem
It works
Thanks so much
5 stars

 

[Jeffrey Mahoney]

If you have repeats in your Product List, you may want to Add a formula in another range that shows only the unique values
Cell R2: =UNIQUE(A:A)

Formula for Named Range stored in Column R. I named mine UPL. Now you can reference UPL in the conditional formatting and data validation
=OFFSET(R:R,1,0,COUNTA(R:R))