Can't figure out a formula for Conditional Formatting- Excel 2011

[so_fistica_ted]

Hello.
I guess this is an easy question to answer but I don't succeed to figure it out.
I attached an Excel 2011 file and what I want to do is this:
In column O I have a list of grocery items and next to each, in column P a number, according to the department it's coming from. 40 items, 8 categories.

Each item is copied to a table (A1:J8) (so that, whatever I change in the list and do a data sort, changes will be reflected in the list).

I want the items and the empty cell to the left of each item in the table to get a color according to the dept. #.
EG: I have 5 items in the produce dept (#1) I want cells A1:B5 to be colored green.
Dept # 2 is dairy, there are 3 items, I want them to stay with the white background and so on, a different color for each group and, of course the moment items/dept numbers change, colors will change accordingly.

Thanks in advance.
Any input will be greatly appreciated.

[Moo the Dog]

For some reason the results I got when testing the first time worked fine, but when I re-opened the sheet the colors were incorrect, and I'm not sure why.

I have removed my suggestion until/ if I can find a working solution (or someone else does).

Sorry!

- Moo

[Moo the Dog]

GAH! I figured it out... Your list of products isn't in alphabetical order... so the formula will need to change to this:

=VLOOKUP(B1,$O$1:$P$40,2,0)=1

I forgot to include the 'Exact Match' argument (,0) in the VLOOKUP. Add that and it works fine.

For each condition you will just have to change the 1 to 2, then 3, etc.

Sorry!

- Moo

Here's the procedure again, in case you need it:

1. Select cells A1:A8
2. Click Conditional Formatting
3. Create a new rule using a formula
4. Paste this formula into the rule box: =VLOOKUP(B1,$O$1:$P$40,2,0)=1
5. Choose the color fill for that rule and click OK
6. Add 7 more rules using the same formula, except change the final number from 1 to 2, 3, etc. and change the fill colors for each
7. Once all the rules are created for A1:A8, copy those cells, then paste them into columns C, E, G and I

[so_fistica_ted]

Thanks Moo.
Yes, I tried your 1st formula and after it didn't work I went online trying to figure how the function works, but then you got back with the corrected one.
And it works perfectly, I have just 1 problem left: "I want the items and the empty cell to the left of each item in the table to get a color according to the dept. #."
The empty cells got the right colors but I didn't find a way to paste the same formula for the item names, so they get the same colors as the empty cells.
Is there an easy way?

[Moo the Dog]

Glad it works...

To answer your question about whether there is "An easy way"... no.

I just made the changes necessary - took me about 5 minutes.

You'll have to do this:

1. Open the Conditional Formatting Rules Manager
2. Where it says 'Show formatting rules for:' select 'This Worksheet' (Which will show all 40 CF rules you previously created)

Where it says 'Applies to:' should be ranges $A$1:$A$8, $C$1:$C$8, and the same for columns E, G, and I. (8 C.F.'s per range). All of those will need to change to include the column to their right. For example:

$A$1:$A$8 would change to $A$1:$B$8
$C$1:$C$8 would change to $C$1:$D$8
etc...

I changed the first one, then copied and pasted it over the 7 other CF's for that specific range. Did that for each specific range of CF's. That will only take a few minutes. Honest.

Then the next part is similar, in that you will be pasting a $ in the formulas (yes, all 40 of them) in a specific spot. Easiest way is to start at the very top condition/formula. Double click on the first formula in the CF Manager window so you can edit it, and place a $ between the opening ( and the cell reference. For instance:

If the first formula is: =VLOOKUP(B1,$O$1:$P$40,2,0)=1 ... change it to =VLOOKUP($B1,$O$1:$P$40,2,0)=1

You'll do that for all 40 CF's. So just double click each one, insert the $ and click OK, and keep going. Again, takes only a few minutes.

Once that is all done, click 'Apply' in the CF window - and your results should be similar to my attached sheet below. Good luck!

- Moo

[so_fistica_ted]

Thank you Moo, appreciate your help!!!
It works great and that's what I needed.
Now I am going to learn exactly and figure how the Vlookup function works, as well as the CF.
Have a good weekend.
Ted