Top/Bottom Rule for Conditional formatting

[thequebs]

Is there a way to use the Top/Bottom Rule to format the background colors on a range of cells where the number of cells that are formatted is based on a value in another cell?

[thequebs]

OTexample.xlsx
Here's a simple example of what I'm trying to do. Based on the "Number Needed" in cell F1, the lowest n values from column C (In my example, 2) should be conditionally formatted based on whether or not they have an x in the "Signed Up?" column B.

[ben_hensel]

I couldn't figure out a way to do it inside the rule itself, but it was simple with the addition of a dummy column in D to increment how many people were signed up, plus two more cells to keep track of relational data. Then it's a dead simple formula rule for conditional highlight.

Anything you don't want to be visible in the final sheet can just be hidden, obviously.

[thequebs]

This doesn't take into account how many hours each person has in column C. The sheet should highlight the 2 people with the lowest hours from that column.

[Jonmo1]

Try

=C2<=SMALL(IF($B$2:$B$6="x",$C$2:$C$6),$F$1)

EFthequebs.xlsx

[thequebs]

I guess I'm not explaining what I need very well. I want the 2 (from F1) people with the lowest hours (from column C) who signed up (from column B) for overtime to be highlighted. So.... They have to have an X next to their name in column B AND have the lowest hours in column C. In my original example sheet it is highlighted how it SHOULD look if it were working the way I need it to.

[Jonmo1]

Umm, the book I posted is using conditional formatting to highlight the same 2 rows that your book highlighted manually.

[thequebs]

In your book, change the "number needed" in F2 and watch what happens. change it to anything from 1 to 5.

Or, move one of the X's from column B. It doesn't highlight the right rows.

[FDibbins]

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =$C2<=SMALL($C$2:$C$6,$F$1)

edit: did not take X into account...
=AND($C2<=SMALL($C$2:$C$6,$F$1),$B2="X")

[Jonmo1]

OK, got it..

Try

=AND($B2="x",$C2<=SMALL(IF($B$2:$B$6="x",$C$2:$C$6),$F$1))

[thequebs]

OK, got it..

Try

=AND($B2="x",$C2<=SMALL(IF($B$2:$B$6="x",$C$2:$C$6),$F$1))

Alright! This one worked like a charm! Now if I can bother you for one more thing... If only one person is "signed up" with an X in column B and the "number needed" in F2 is higher than that I'd like for it to go ahead and format the lowest hours person that isn't X'd in column B. Does that make sense? And is it do-able?