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. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,206
    Thanks
    14
    Thanked 331 Times in 324 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 07:03. Reason: added PS

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

    Poindexter (2011-03-18)

  5. #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.

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

    Poindexter (2011-03-18)

  7. #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

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