Sunday, November 9, 2008

Open Office: Is there an easier way?

Question for any OpenOffice/StarOffice spreadsheet users (and perhaps Excel users):

Q: I want to format an entire row based on the value of one cell in that row. I found one way, but is there an easier way?

I'm using the Conditional Formatting feature. By default it only formats a single cell based on that cell's value, but I made the following changes to format the entire row based on the value in COLUMN "Q".
In this example, if the cell value in COLUMN "Q" is "yes" then I change the style for the whole row to "My Style."
  1. Go to Styles and Formatting from Format menu.
  2. Define style "MyStyle"
  3. Select all of the rows I want this to affect.
  4. Go to Conditional Formatting from Format menu.
  5. Change condition to "Formula is"
  6. Use the following formula:
  7. Set the style to "MyStyle"
This works but hard codes the column value of "Q." If I add or delete any columns before Q then the formatting turns off and I have to go in and change the value. Not a huge problem but this doesn't seem very elegant. Also, you can only have up to three conditions per cell, so that makes for a maximum of only four possible states (formats).

So, are there any better ways of doing this?