Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Conditional Format ? (Excel 2002)

    Hi

    I would like to Conditional Format a cell based on a date in another worksheet.

    example if Sheetname (Summary) contains a date of April 04 in cell C2

    In sheetname (2004) I would like to Conditional Format the cell F2 if C2 in (summary) => April 04.


    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Format ? (Excel 2002)

    You can't use references to other worksheets directly in conditional formatting. There are (at least) two workarounds:

    1. Select cell C2 in the (Summary) sheet.
    Click in the cell address box on the left hand side of the formula bar and type a name, e.g. TheDate.
    Switch to the 2004 sheet, then select F2.
    Select Format | Conditional Formatting...
    Select Formula Is from the first dropdown list.
    Enter a formula in the box next to it. The exact formula depends on the contents of the other cell. If it contains April 2004 as a text value, use

    =(TheDate="April 2004")

    but if it contains a date value, use

    =AND(YEAR(TheDate)=2004,MONTH(TheDate)=4)

    Click Format... to set the format you want.

    Or, alternatively

    2. In an empty cell in the 2004 sheet, say A1, enter the formula

    ='(Summary)'!C2

    and use this cell in conditional formatting. Proceed as above, but use the cell instead of TheDate.

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

    Re: Conditional Format ? (Excel 2002)

    Go to cell C2 on the Summary sheet and name the cell (I named it SummaryC2 for this example). Then go to sheet 2004, select cell F2, go to conditional format and select "Formula is" in the drop down list, and enter this formula:

    <pre>=SummaryC2>=DATE(2004,4,1)
    </pre>


    Replace SummaryC2 with whatever you named the cell. Select the formatting you want and click OK.
    Legare Coleman

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Conditional Format ? (Excel 2002)

    Hi Hans


    Thanks to you and Legare for your response, once again I am spoilt for choice

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Conditional Format ? (Excel 2002)

    Hi Hans

    If I may I would like to extend my question. Please see attached sheet

    If possible Where P2 = April-04 I would like to format April to Dec grey and Jan to Mar yellow.

    And other rows accordingly

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Format ? (Excel 2002)

    Select C2:N4 (or as far down as you want to apply conditional formatting)
    Set the background colour to yellow using the Fill Colour button on the toolbar or the Pattern tab of Format | Cells. This will be the "default".
    Select Format | Conditional Formatting...
    Select Formula Is from the first dropdown.
    Enter the following formula in the box next to it. Take good note of the use of $ characters.
    <code>
    =DATEVALUE("1"&C$1&YEAR($P2))>=DATE(YEAR($P2),MONT H($P2),1)
    </code>
    Click Format...
    Activate the Pattern tab.
    Select the grey color you want.
    Click OK to close the Format dialog.
    Click OK to close the Conditional Formatting dialog.

    Because some browsers mess up the 'greater than or equal' in a post, a screenshot of the formula is below

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Format ? (Excel 2002)

    If you'd prefer to leave blank cells yellow, proceed as above, but use this formula:
    <code>
    =AND(NOT(ISBLANK(C2)),DATEVALUE("1"&C$1&YEAR($P2)) >=DATE(YEAR($P2),MONTH($P2),1))
    </code>
    in the conditional formatting dialog.

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Conditional Format ? (Excel 2002)

    Hi Hans

    That is exellent, thank you is not enough.


    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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