Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format Issues (2003)

    Lounger's I have two problems that I'm sure someone can help with:-
    1. Conditional Format - I have a column that has dates of a month - ie 1/1/05 through to 31/12/05 and another that contains dates for public holidays - I want to be able to set a conditional format so when a date in the month column equals a date in the public holidays column to conditional format activates, &
    2. I have cell that calculates a %, formatted to 0 decimal places, when I add & "text" ie '46% of the total', the cell returns a value of "0.461538461538462 for the total"


    any thoughts?

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Format Issues (2003)

    1. For your conditional format, assuming the date list starts at A1, and the Holidays are in a range called 'Holidays', in cell A1 use "Format | Conditional | Formula is" and enter

    =ISNUMBER(MATCH(A1,Holidays,0))

    set the desired format, and copy the format down.

    2. For combined values and text in a cell, use the =TEXT(argument,format) function combined with text such as this:

    ="This months' widget inventory value is "&TEXT(A1/B1,"#0%")&" of the total"
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Issues (2003)

    Thanks John - Will give it a go

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Issues (2003)

    You can't really copy a conditional format down. You have to select all of the cells you want conditionally formatted before you go to Format | Conditional Format.
    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Format Issues (2003)

    I don't agree with you, Legare. To make sure, I just repeated my advice above and then copied the format down using the format Painter tool; it works.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Format Issues (2003)

    I don't understand.

    I copy cond formats down a column just like other formulas/formats. Autofill will even work to copy the formats.

    You can also select the cell with conditional format
    press the "Format Painter" button and then select the range to put the same format. It works the same as doing the selection first: the format refers to the "cell" or the "relative cell" if based on a formula.

    You can also copy - paste special - formats
    Steve

  7. #7
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Issues (2003)

    I would agree with John and Steve on this.

    Just to add one more thing. In general, you do have to be careful with your absolute and relative cell references when copying conditional formats. John's conditional format formula doesn't have any absolute vs. relative reference issues.

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Format Issues (2003)

    Yep, I frequently relearn the absolute vs. relative reference lesson the hard way! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Format Issues (2003)

    <hr>you do have to be careful with your absolute and relative cell references when copying conditional formats<hr>

    This is good advice whenever copying a formula, whether it is cell formula, conditional format formula, a validation formula, or even a named formula.

    Steve

  10. #10
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Issues (2003)

    Once a conditional format is created in a cell, it can be copied down, accross or even to specific cells on the sheet. Say the conditional format is already created in cell A1 and you need it in cell C5, E8 and F10. You can copy A1 and then select C5, E8 and F10, use paste special and under paste, select formats. Conditional formatting will be copied as well as any other formatting from A1.
    As stated by the other loungers, attention needs to be paid to absolutes and named ranges.

    yoyoPHIL

Posting Permissions

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