I am an Equipment Operator with the Navy Seabees, and the Yard Boss at Camp Moreell in Kuwait. We have CESE (Civil
Engineering Support Equipment) going into IEM (Inactive Equipment Management). What this means; our CESE has to be cycled
(checked for preventative maintenence issues, started up, ran...) once a week if it hasn't been used within a seven day
period, but CESE going into IEM only needs to be cycled once per month.
Column E in my report reflects that as a
two part eight day graph, with the formula =TODAY()-8 for the shortest end of the bar graph and the red of the graded color
scale, and =TODAY() for the long end of the bar and the green of the color scale. If a piece is cycled today I enter today's
date and the bar shows full and the scale is green. As that date approaches 8 days past, the bar moves to the left and the
scale changes yellow to red.
I need that graph (column E) to change to a thirty day scale when I change the
LOCATION (column D) to IEM, back to an eight day if I change it to DISPATCH, and to clear out completely if I change to any
I've tried a few different things, but can't seem to get any of them to work. One thing I tried
that I thought would work--but didn't--is to make column H (which I planned to hide) equal eight if the corresponding row in
column D equals DISPATCH (using an IF formula), and to equal thirty if it's IEM. Then I wrote my graph formula a few
different ways to the general effect of =TODAY()-H:H but no way that I wrote it worked. I couldn't figure out a way to just
make DISPATCH equal a hidden value of eight, and IEM to equal thirty, but since the formula I did for the method I tried
didn't work anyway, I wouldn't have gotten that to work either. I was able to get column H to reflect eight or thirty in
accordance with column D, but I can't get the graph to cooperate.
I also tried entering the formula
=IF(D:D="IEM",TODAY()-31,TODAY()-8) into the Conditional Formatting formula field for the color scale and data bar,
but I get the error "You cannot use relative references in Conditional Formatting criteria for color scales, data bars,
and icon sets."
What's my work around?