Conditional formatting - text belong a specific group

[phillipbrighton]

Hi everyone, first of all sorry if my English isn’t the best, i’m Portuguese.


I’m new to the forum and i’m learning excel from my own. I’m trying to make a personal budged worksheet.


The problem i have now is this:

I have 2 columns and every cel of those 2 columns are combo box , the same thing you have here in the site the “excel data validation techniques”


Now what i want is this:

If the text in the second cel doesn’t belong to the group in the first cel, then retrieve the text in red.


I can do this with this code


MATCH($D$6;OFFSET($C$9;MATCH($B$6;$B$10:$B$22;0);0;COUNT.IF(B10:B22;$B$6);1);0)


I don’t know if this is correct but this show me the line of the value in the second column or ‘#N/D’ If the value doesn’t belong to the group in the first cel,


Then in conditional formatting I put a function to retrieve true or false ( in Portuguese is "É.Não.Disp()" I don’t know that in English

but I only can do this in one line, and I want to do for all the lines in the table.


How can do this?

Hope you guys understand this.

thank you

 

[Hui]

Phillip

Do all the cells in the first column lookup the same area for validation ?

If true - Where abouts is that ?

 

[phillipbrighton]

Hi Hui, yes the first column lookup for the same value.


it works like this:


title B C title list

B B1 C1 B B1 Title list

C B2 C2 B B2 B B2

D B3 C3 B B3 C C3

E B4 C4 B B4 D D3

B5 C5 B B5 E E4

B6 B B6

B7 B B7

B8 B B8

0

0

C C1

D E C C2

D1 E1 C C3

D2 E2 C C4

D3 E3 C C5

D4 E4 0

E5 0

E6 0

E7 0

E8 0

D D1

D D2

D D3

D D4

0

0

0

0

0

0

E E1

E E2

E E3

E E4

E E5

E E6

E E7

E E8

0

0


The first one is all the head lines in a list, so in the first combo box (the last table in the example) you have that data.


The second combo check the thirth table for the correct values, like the example from chandoo. I do this because all the info are customized so on the combo box it only appear the valid info.

 

[phillipbrighton]

Hui, yes they do.


This is the combo table


Title list

B B2

C C3

D D3

E E4


All the cells are combo box.

The info for the first is from here


title

B

C

D

E


And for the second one it will check this table


title list

B B1

B B2

B B3

B B4

B B5

B B6

B B7

B B8

0

0

C C1

C C2

C C3

C C4

C C5

0

0

0

0

0

D D1

D D2

D D3

(...)


It has 0 because is reading a costumized table so you can add/delete info. and if 0 it doesn't appear on the combobox.

 

[phillipbrighton]

forget this i find the way, just copy the first cel with the conditional formatting, along with the reference code for each line to return true or false