Conditional Formatting for multiple specific texts

[Pistolari]

I have two sheets in one workbook, sheet1 is the values that I use as a referral to sheet2 which has the real work.


in sheet2 I have a column named (Positions) which I put the position of each new employee
in sheet1 I have a column that has all the types of positions in our company.

how can I make the cells in (Positions) column in Sheet2 to be marked in a color if it wasn't from the list of all company's positions?

Thanks in advance

 

[Special-K99]

Indicating what columns the positions are in would have been helpful.
Since you haven't I will assume the following (adjust the formula for your data):

Sheet1 column A has company positions
and Sheet2 has columns A and B as Name and Employee position.

You want to know what Employee positions (Sheet 2 Column B) do not appear in Company Positions (Sheet 1 Column A)?

Select Sheet2 Column B

Conditional Formatting
New Rule
Use a formula to determine...

=ISNA(VLOOKUP(Sheet2!B1,Sheet1!A$1:A$1000,1,0))

Format as required

 

[Pistolari]

Special-K999 thanks a lot but i will explain more.

sheet2 has column A as position, so when any employee wants to join and i make him an offer letter in our company i put his position column a and complete his information in his row

now my company made a new policy that they categorized the positions, for example before we had painter, special painter, civil painter and many more.................... now the company wants to add any new position as (painter) only, so i wanted to make a list of positions in sheet1 according to the new company policy, and continue working on sheet2 which has all the details of employees, but when i add an employee and put him as (civil painter) i want it to be highlighted in red that shows me there is no (civil painter) in the list of sheet1 column A


So in sheet1 i have column A that has list of positions, and in sheet2 column A i also need to add positions raw by raw but if i enter a text in sheet2 column A that is not in the list of column A sheet1 i want it to be highlighted in a colour.


im sorry if i didnt explain the idea well.

 

[Special-K99]

Just adjust the formula based on what I've assumed and what you have.

Select Sheet2 Column A
Follow the instructions above
Change the formula to

=ISNA(VLOOKUP(Sheet2!A1,Sheet1!A$1:A$1000,1,0))

 

[Pistolari]

Special-K99 it tells me (you may not use refrences to other workbooks for conditional formating) although im in the same workbook.... any idea?

 

[Pistolari]

ok wait wait it worked !!

thanks bro, now i have a question as level 2 hehehe

what if my column was B not A, this way vlookup wont work i believe

solve it and take an apple

 

[Special-K99]

Aww ****! You're right you can't specify another sheet in condtional formatting
The way you get round this is to define a named range which you can access in CF on any sheet.

Define Sheet1!A$1:A$1000 as a named range, call it anything you like LISTX for example then change the formula to

=ISNA(VLOOKUP(Sheet2!A1,LISTX,1,0))

 

[Scott T]

Rather then checking if the entry is valid you could use data validation to make sure it is on the list of positions.

 

[Special-K99]

I dont know how you go tround it, you usually need to define a named range as just stated.
Following on from that
define a named range A$1:B$1000 and modify the formula thus

=ISNA(VLOOKUP(Sheet2!A1,LISTX,2,0))

Note the column selection has changed from 1 to 2.


Please note: When people describe a problem and then change it several times and add things because they haven't planned out what they want beforehand...I start to lose interest. You're ok so far but I am at work and have very little time to spend on these problems hence I cant keep coming back to them.

 

[Pistolari]

thanks a lot bro you did more than i needed.

+recommended