Concatinated list column value validation with another list in Excel 2013

Question

I have 3 columns(A,B,C) in my Excel sheet with first two columns(A & B) having the selection list as {a,b,c,d,e,f,g,h,i} and the third column(C) will get auto populated with the value based on the selections made in A & B as A1-B1

Eg:

||   A   |   B  |    C     ||
||   a   |    f   |   a-f    ||
||   d   |   g   |   d-g   ||
||   c   |   h   |   c-h   ||

Here I need to validate the values in column C against the list {a-c, d-g, c-f}, if the value is not existing the list, it should block the selection in A and B columns by showing error message.

I am using MS Office 2013

Answer 1

This is harder than I thought but here is how you can "sort of" make it to work. Maybe there is another way but this is the idea, see the attached pic, that I came up with.

1. Columns E, F and G: These are the lists that you want to check against with. The Good List is all entered manually. And columns F and G are basically retrieve those good values from the Good List. See row 6 for formulas. I also made range names as GoodList > column E, GoodListLeft > column F and GoodListRight > columns G.

2. Columns A and B: These two columns are also entered manually. No drop down is implemented here. But on the row 9 you can see the validation rule (Data > Data Validation > Allow field choose Custome, and use the formulas on row 9 into Formula field). Row 9 is the formula for Data Validation.

   On cell A2, enter =COUNTIF(GoodListLeft,A2)=1, drag or copy down so you don't need to enter them by hand. Note that I used A2 so it will use relative reference when you drag down.

   Cell B2 will be =COUNTIF(GoodListRight,B2)=1.

3. Cell C2 I have this formula, =CONCATENATE(A2,"-",B2) so it will concatenate the strings from columns A and B. But you will need to use =COUNTIF(GoodList,C2)=1 in the Data Validation Formula.

   See this pic for Data Validation set up:

   

4. Conditional Formatting: Under cell A2, select Conditional Formatting > New Rule > Use a formula... and enter this formula, =COUNTIF(GoodList,$C2)=0. See pic below.

And this should apply to =$A$2:$B$8

Hopefully I explained this clearly. But let me know if you have any problem to implement this.