Conditional Format Puzzle for me

I've hit a snag in trying to apply this conditional format that I'm not sure where I'm screwing up.

Column E has different names "Bob", "Roy", "Tom" all selectable from a drop down box.

Column P has a value just standard numbers, 5, 6, 7

Each name has a different range to complete so Bob has to get between 5 and 10, Roy has to get between 4 and 8 and Tom has to get between7 and 11. However I want the cells in Column P to turn red if they are not between those numbers.

I tried several things including having the default format for Column P be red and then add three conditional formats like this =AND(E6="Bob",P6>4,P6<11) then the format code would turn it black and of course I made a code for Roy and Tom with their values plugged in. However I can get it to work.

Any Ideas where I am going wrong? Plus I'm adding this from $P$6:$P$7012 so it may be something I'm doing wrong there.


Thank you for any help or advice

It would help if you attached a sample Excel workbook, so we can see exactly how your data is laid out.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Note that the Paperclip icon does not work.

Pete

Sorry, here is a quick mock up. Again I would like it to turn red if it is not within that range for the person named in column E.


Thank you for your assistance

I've added From and To values in columns U and V against each name, then highlighted the cells from P6 to P14 and clicked on Conditional Formatting | New Rule | Use a formula... , and then put this formula in the dialogue box:

=AND(ISNUMBER(MATCH($E6,$T$5:$T$7,0)),$P6>=VLOOKUP($E6,$T$5:$V$7,2,0),$P6<=VLOOKUP($E6,$T$5:$V$7,3,0))

Then I clicked on the Format button and chose a red Fill colour with a white bolded foreground colour (on Font tab), then clicked OK twice to exit the dialogue box.

All you need to do is change the Applies to range when using CF | Manage Rules

Hope this helps.

Pete

Wonderful fix Pete, You saved me a lot of typing