Another Excel question; Operations triggered by formatting

Factual Questions
1

Excel 2003 here. I know the use of conditional formatting - where a particular format is used if a particular condition is met.

Can this work in reverse? In other words, if the contents of a cell are in boldface type, can this be used to control what the contents of another cell are?

To explain further, I’ve got a spreadsheet that contains repair order numbers for a group of technicians, along with the time spent on each repair order (hereinafter referred to as RO numbers). When the order is competed, the RO number for that job are all changed to boldface type. If the RO is not completed, the type remains standard.

It would be real handy if a column could be set up where the time spent on all RO’s that are still incomplete could be listed . Something like this;

=IF(Format=bold, list time, leave blank)

By this method I could keep a running total of the time invested in all incompleted repair orders.

Is something like this possible? Thanks in advance to all you Excel experts.

2

Sorry - got my IF statement backwards. It should be,

=IF(Format=bold, leave blank, list time)

Maybe this is why I sometimes have trouble with Excel!

3

You probably need to use VBA for that. Examples.
It might be easier, instead of manually bolding/unbolding orders, to have a separate “Completed?” column that shows 1 for completed orders and 0 for not. Then you can do conditional formatting AND what you’re trying to do here.

4

Reply, I think you may be right about this. And that wouldn’t be too hard. Thanks

5

Probably a better option* to consider is having a separate column “Completed” and entering “True” there when the job is completed. Then you can use the True/False state of that to control things, like totaling all the non-completed repair orders.

  • This is a classic violation of a database design principle, that one column should contain only one data characteristic. You are trying to overload the RO number with the completion status, using odd things like the font of the fields. This kind of database design error nearly always leads to problems, eventually.
6

I wholeheartedly agree with t-bonham. My approach, however, would be to make something a little more useful than a “completed” column containing TRUE or FALSE. I’d add a “date completed” column. If there’s a date, it’s completed. If there’s not, it isn’t. This allows you to not only look at all incomplete ROs, but also ROs that were completed during specific date ranges – or sort the whole thing based on RO completion date.

And, to keep consistency with your current format, use conditional formatting to make the RO# bold if there’s a date in the “completed” column.