If cell, highlight

[bradg12]

Hi All,

I've created a roster in excel for my workplace.

I'm using a few drop down menus, to allow the person creating the roster to select from a list of times in the start and finish columns, along with a formula that finds the difference between the dates to show the number of hours working.

What I want to do, in addition to this, is add another page which displays the shifts in a bar graph type style. I've included a screenshot of how it would look with me physically doing it.

I'm thinking for the formula it needs to be something like;

in the 830 box, if the start time is between this time, colour the cell.

Any ideas on where to start?

Roster:

Graph I want to create based on roster start and finish times

 

[moonfish]

There could be a difference in significance somewhere. Try deleting the value in 'ROSTER TEMPLETE'!B8 and entering 17:45 in it.

You can also try the following:
- Copy the formula formula for conditional formatting into the topleft cell of the area affected by it and copy it across the entire area. This should show a grid of TRUE/FALSE statements describing which cells ought to be colored.
- Select a cell where the color is not what you want it to be.
- In the Excel 2007 menu go to Formulas > Evaluate Formula. Go through each step carefully and try to find where it goes wrong.

 

[bradg12]

If I physically enter in 17:45, it'll highlight the correct column.

If I use the drop down menu it'll start highlighting at 18:00.

If I type in 17:45 in another sheet and use =SHEET to get 17:45 it'll highlight the 17:45 column.

So from what I can tell, it does not like me using the drop down menu.

 

[moonfish]

Where do you store the dropdown menu items? If you make a dropdown menu through data validation, based on the cells in row 2, it should not give you any problems.