Dependent Validation with only one item

[raymhuber]

Hi, I need to validate the data in one column based on the data in the column to the left of it. Basically, I have a product code in column b and a product description in column c and I need to make sure that each code has the correct description. On a separate sheet I have a list of codes (col E) and their corresponding descriptions (col F). How would you set up data validation so that for the list of codes checked in column C an error will be flagged if it does not have the corresponding description? I know how to use indirect to make dependent lists, but obviously I don't want to make a new defined list for all of the product descriptions.

Thanks,
Ray

 

[tybaltlives]

Maybe something like this will work for you. Its just the bare bones. You would also need to add a check if the product code is not present in the true table. Also, everything is on one sheet to make it easier to demonstrate. The validation column is D.

Excel 2010
	B	C	D	E	F
1	product code	Product description	validation	True code	True description
2	A	Product A	OK	A	Product A
3	B	Product B	OK	B	Product B
4	C	Product C	OK	C	Product C
5	D	Product E	Check	D	Product D
Sheet3

Cell Formulas
Range	Formula
D2	=IF(VLOOKUP(B2,$E$2:$F$5,2,FALSE)=C2,"OK","Check")
D3	=IF(VLOOKUP(B3,$E$2:$F$5,2,FALSE)=C3,"OK","Check")
D4	=IF(VLOOKUP(B4,$E$2:$F$5,2,FALSE)=C4,"OK","Check")
D5	=IF(VLOOKUP(B5,$E$2:$F$5,2,FALSE)=C5,"OK","Check")

 

[raymhuber]

This looks to be almost exactly what I need, thanks!

 

[tybaltlives]

thats cool. glad I could help.