Conditional Formatting-Greater than or equal to rule

0

Hello experts,

I want to apply conditional formatting to column Total Experience to display only those employees having experience of 5 years and above. 

Kindly guide how to apply conditional formatting for such rule.

Thanks and Regards,

Akash Sharma

Answer
Discuss

Answers

0
Selected Answer

Hello Akash,

I guess you want to get the details of the employees with an experience equal to or more than 5 years in  a new sheet. 

If it is so, please check the attatched file.

I've followed 4 steps which have been mentioned in Sheet3. Hope you'll be able to modify the result as per your need.

Please click here for the file.

Edited: 

What I've done to your data: (in a new sheet, i.e. Sheet3)
1. Called the names equal to or greater than 5  with: (starting from B2, dragging down to B51)

=If(Sheet1!G2>5,Sheet1!B2,"")

It left behind the names with less than 5. So, there are blanks in in column B.
2. The following code helped to collect the scattered names together leaving the blanks (Starting from C2 and dragging down to C51):
=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(B:B,SMALL(IF($B$2:$B$51<>"",ROW($B$2:$B$51)),ROWS($C$2:C2)))))

It is an array formula, so you'll have to press Ctrl+Shift+Enter after typing in cell C2. This will show the formula covered with {}.

If a sign like ' appears before =, it must be deleted.

That's all you need to do!

But if you want other details of those employees, you can use vlookup like:
=Vlookup(C2,Sheet1!B2:I51,2,False)

It will look for the value second (2 in formula) to the name from a range of B2 to I51.

On the other hand, if you want to highlight names based on their values in Column G,
> Select the column of names (e. g. click on the Column head letter 'B'),
> Go to Home > Conditional Formatting > New Rule > Use a formula > type:
=G1>=5

> Format with the color of your choice >Ok > Apply >Ok.

I hope this helps.
Discuss

Discussion

Hi Chhabi

Thanks for giving your valueable time and effort in helping me out. I have not understood lookup function that you have applied probably that would come under advanced excel.
Can you suggest any rule in conditional formatting to obtain total exp 5 years and above out of the employee list?

Thanks and Regards,
Akash Sharma   
Akash Sharma (rep: 40) May 9, '18 at 12:14 pm
Please check out the edited answer.
Chhabi Acharya (rep: 111) May 9, '18 at 2:59 pm
Resolved thanks
Akash Sharma (rep: 40) May 20, '18 at 1:26 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login