Populating multiple cells by entering in criteria

**Mothman** · 01-29-2010, 12:46 PM

Hey guys,

I am adding a sheet with a graph I made. There are two sheets, the first with the graph, the second with tables. What I would like to do is populate the graph based off what is entered into the tables. My main goal is to get the cells in the cells in the graph for each day to fill in color between the given time frame entered in on the table. Obviously I would be rounding up or down to make the time end in ":00."

The color I would like to fill the graph with is shown on the graph sheet.

Also, if anyone can actually get this to work (I honestly wouldn't know where to start) Is there a way to have multiple colors and a possible overlay of colors?

Lets say on tuesday I have 10:00-15:00, and 12:00-18:00. Can I have two colors populate the cells and a thirs color populate the cells in the overlay which would be 12:00-15:00?

Just let me know if this is even possible. I'm not too familiar with macros, so if you have a cool macro that might work, please explain how to format it. Thanks.

**6StringJazzer** · 01-29-2010, 01:53 PM

I am getting a vague idea of what you want to do but we need to clear a few things up:

Originally Posted by Mothman

Lets say on tuesday I have 10:00-15:00, and 12:00-18:00. Can I have two colors populate the cells and a thirs color populate the cells in the overlay which would be 12:00-15:00?

Not sure how you want to use the first two colors, but this is all possible if we can get a more precise spec. No macros required, it can be done with conditional formatting.

Your table only shows one start and one stop per day. How can you specify two sets of times, which may be overlapping as in your example?

I attached an example as far as I could take it, with one range of times per day and one color. Feel free to play around with the times I entered on Sheet2 to see if it does what you want.

**Mothman** · 02-05-2010, 09:17 PM

That does what I'd like for it to do. What would be the best way to specificy multiple times to create an overlap? I just threw in one table of times for the sake of seeing if it could work. Feel free to add whatever is needed to create the overlap with multiple time frames.

**Mothman** · 02-05-2010, 09:50 PM

Here is an update to the spreadsheet. I've added multiple tables for different time frames on same day. Also, the "Risk Ranking" I would like to populate the cell as red, yellow, or green based off which one contains a non-blank. If possible, is there a way to do this with an image? Say that I had three images, all the same type of image, but different colors, could it populate the cell with the specific image color based off what is non-blank in the table?

**Mothman** · 02-05-2010, 10:10 PM

Alright,

So I expanded the formula to the entire table for multiple time ranges, using the VLOOKUP formula in conditional formatting. The only problem I am running into is the overlap. Will I need to make extra sells on the data table to specify as overlap? Or is there a formula I can add to conditional formatting to recognize the overlap and color it in? Please help. Thanks.