Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2010
    Location
    Oregon
    Posts
    7
    Thanks
    2
    Thanked 1 Time in 1 Post

    Applying style to cells based on another cell's contents

    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    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
    Last edited by sdckapr; 2011-03-17 at 08:03. Reason: added PS

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    Poindexter (2011-03-18)

  4. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    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.

  5. The Following User Says Thank You to Gfamily For This Useful Post:

    Poindexter (2011-03-18)

  6. #4
    New Lounger
    Join Date
    Feb 2010
    Location
    Oregon
    Posts
    7
    Thanks
    2
    Thanked 1 Time in 1 Post
    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

  7. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •