+ Reply to Thread
Results 1 to 6 of 6

Conditional Formatting Based on Values on Another Sheet

  1. #1
    Registered User
    Join Date
    03-19-2013
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    15

    Conditional Formatting Based on Values on Another Sheet

    Many questions on conditional formatting, but (of course) none specific to the problem I am having. So, here goes a new thread.

    Excel 2010

    What I am looking for:

    On sheet 3, in cell C58:
    bold red font IF sheet 1, column A:A=sheet 3, A58 and IF sheet 1, column H:H>2

    For clarification:
    Sheet 1 is my report detail (run daily); column A is the report date, column H is a numerical value
    Sheet 3 is my statistics information; column A is the report date, column C is a record count (how many records for that date)

    I need to have the font in sheet 3, column C to be bold red if any of the numbers on sheet 1, column H is > 2, for that report date

    The number of records (in the detail) for each date can vary, so I cannot have a constant cell reference for the formatting
    I will need to copy this formatting all the way down column C, as the statistics are a running report, each line for the next days data summary

    Thank you!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Conditional Formatting Based on Values on Another Sheet

    It would be easier to offer help if you provided a sample workbook, but try this as a new rule in teh cell you want to change color...

    =max(sheet1!H:H)>2
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    03-19-2013
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Conditional Formatting Based on Values on Another Sheet

    Thank you, Ford.

    I will try to put together a sample for you.

    Regarding the formula you posted, the cell where I need the bold, red font is indeed if > 2, but also, if the report dates match. I may have 10 lines for today's report ... there were 2 lines which had the numerical value (in column H) > 2, but in yesterday's report (7 lines), there were no values > 2. So, in my statistics page, yesterdays totals would be in regular font, todays totals would be in bold, red font.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Conditional Formatting Based on Values on Another Sheet

    ok maybe formula...
    ={MAX(IF(A2:A7=F2,H:H,0))}

    where column A is your dates and F2 is the date you want to test for

    Because this is for CF, you dont need to enter this as an array formula

  5. #5
    Registered User
    Join Date
    03-19-2013
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Conditional Formatting Based on Values on Another Sheet

    Thank you, Ford. I am attaching a sample report. The column designations are changed slightly as I had to put together a representative report, without sensitive information.

    Disclaimer: Information contained in this report does not represent any actual companies or company information.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-19-2013
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Conditional Formatting Based on Values on Another Sheet

    Ford,

    When I tried your formula, I received the error message "The formula you typed contains an error". The formula I used was:
    =MAX(IF('PURCHASE ORDERS'!A:A,A72,'PURCHASE ORDERS'!H:H,>2))

    Where did I go wrong? Were you able to work with the sample excel sheet I posted?

    Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 09-25-2013, 10:05 AM
  2. Conditional Formatting based on values from a Pivot
    By Rajula in forum Excel General
    Replies: 9
    Last Post: 10-17-2012, 02:52 AM
  3. Replies: 2
    Last Post: 08-27-2011, 03:05 PM
  4. Conditional formatting based on values in A1:A6
    By kpierce in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-28-2011, 11:44 AM
  5. Conditional formatting based on another sheet
    By goofy78270 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-22-2007, 04:19 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1