Hello all,



I have a workbook that contains about 6 printable pages and one raw-data page. The people that are on my list are assigned a color-group at work, but they change out pretty frequently so I have the workbook set up to reference the raw data, and make up the print-sheets automatically.

One of the cells in the raw-data sheet says the name of the color assignment (ie; Yellow), and I would like to use this information to format the fill-color of the appropriate cells in the print-pages. I don't want the word "yellow" to appear on the print pages, but I want the data in that person's row to appear with a yellow fill.

Multiple cells with multiple data types will reference this color field, so I can't just have it conditioned to say "fill yellow if name = John"... instead I'd like that person's name, room number, job code, etc, all to fill yellow by having the cells look to see what color is listed for that person.

Any ideas? I've tried some things with conditional formatting, and while I do have the idea that the answer lies somewhere in the "formatting based on a formula" section, I can't figure out how to make it work.

Thanks!
~Joe

You said one of the cells in each worksheet has the name of the color, right? So that would be the formula: Color particular cells yellow if $X$1="Yellow" (or whatever). Wouldn't that work for you?

Hi Bob, thanks for the reply.

Yes, there is a cell with that info, but it's only on one of the sheets, not on each sheet. Will this work? I tried putting in a formula for doing that based on examples of similar things I've seen (but nothing exactly like what I'm looking to do), but I must admit I'm not a formulas-expert.

For example, the raw-data is on sheet1, and it's just a simple spreadsheet with everyone's information in a table. On sheet2, I've got a list that pulls up everyone's name, phone number, job code, and assignment number. On sheet3 I have a list that pulls up the names, titles, and certificate types, etc... for 6 lists based off of that sheet1. The group color is listed as one of 5 color groups on that sheet1, and on a few of those other sheets I want it to highlight the person's name, number, etc, in their group color, without having to print the words for the colors.

For the test one that I've been playing with, I've been trying on sheet2 to have the Conditional Formatting reference that word "Yellow" or "Red" or whatever on sheet1, but I can't sort out the formula to get it working. Whatever formula I try, it comes back saying that there is an error in my formula.

In the Conditional Formatting options, I set it to "Use a formula to determine which cells to format", then I enter the formula as I think it would work. The one I have at present is as follows;

=Data!$H$3,"Yellow"

(I've named the raw-data sheet1 as "Data", fyi)

I figured this would be a good starting place, because I could get it to highlight it yellow for that one, but it won't even do that.

Once I get that sorted out, I'll then need to expand that formula to allow it to highlight based on whichever of the 5 colors is listed (Yellow, Red, Pink, Blue, and Green).

Any ideas?

Thanks,
Joe