Applying style to cells based on another cell’s contents

Topic

New Reply

First, using XP and Open Office 2.4

I have 4 custom styles, Style 1, Style 2, Style 3, Style 4, mostly different fill colors and font sizes.

Columns A & B are text data (specifically name and address). Column E has a one number code, 1, 2, 3, or 4.

Can the number in Column E make Column A be styled with one of the 4 custom styles?

In other words, I want to enter a 3 in cell E1 and have Style 3 applied to cell A1, or have the style applied to both E1 and A1.

Is it possible to do this and how would I go about it?

I thought conditional formatting would work, but if I understand what I read, CF only works on one cell independently and is based only on a numeric value in the specified cell.

Thanks for any information.

Reply | Quote

Replies

You can use conditional formatting using a formula (selection in the first box). The formula can look another cell in the worksheet.
You could explicitly format as Style1, then use for the 3 conditional formats
=E1=2
=E1=3
=E1=4
and have each be that style.

Thus A1 will be either Style 1-4 depending on E1
Steve

PS I presume that CF works in OpenOffice like it works in XL. I don’t use OpenOffice

Reply | Quote

In Open Office 3, Conditional Formatting works in the same way as in Excel 2003 – so you can have 3 special formats that apply in addition to the default format.
What you need to do is select columns A and B, then bring up the Conditional Formatting dialog

Select the 3 tick boxes, and use the “Formula is” dropdown.
What you then do is write the following formulae against each rule
=$E1=1 calling Style 1
=$E1=2 calling Style 2
=$E1=3 calling Style 3

You need the dollar sign because you want both columns A and B to have the same style set depending on what’s in column E.

I would attach a demo file, but I’m getting an ‘Invalid File’ message when I try.

Reply | Quote

Thank you both for your answers.

The bottom line is I finally got there, but it took several hours. Before anything else would work, I had to apply CF to column E.

When I entered the formula for column A, it applied the style to the line below where it was supposed to be. If E11 was a 1, cell A12 had the style instead of A11. I finally figured out that’s because the first row is locked with a title for each column. When I changed the formula to $E2 instead of $E1 it worked perfectly.

I am grateful for your help specific to this spreadsheet, but more importantly you taught me a bigger lesson about how powerful CF can be, using formulas.

Poindexter

Reply | Quote

Whenever you create a CF formula the formula is relative or absolute (using the $) based on the ACTIVE cell. To use the formulas that were suggested required that you set the CF starting on row 1. If you selected row 2 and downward, and used E1 in the formula, it is not surprising that it was off one row…

Steve

Reply | Quote