+ Reply to Thread
Results 1 to 10 of 10

Conditional Formatting based off a another Tab

  1. #1
    Registered User
    Join Date
    05-13-2013
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    16

    Angry Conditional Formatting based off a another Tab

    I have a set of numbers in one tab(numbers from the week prior) of a worksheet and the exact statistics in a second tab(current numbers of the week) and want to compare the two associated tabs by taking the most recent tab's numbers and coloring the cell if it is greater than 10%, less than 10%, or no color if in between this variance. An Example of numbers is below:

    Prior Week
    12125
    553
    194734
    54510
    1630
    1092
    1318
    68801098

    Current Week

    12127
    555
    39
    194736
    54390
    1633
    1092
    1318
    59976844

    I have tried all sorts of ways to do this and just cannot get it right!

    Thanks in advance!
    Last edited by Jbryantbaker; 05-13-2013 at 04:50 PM. Reason: Solved

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional Formatting based off a another Tab

    Welcome to the board..

    The ABS function is pretty good for this

    Something like

    =ABS('Prior Week'!A1-'Current Week'!A1) > =10%

  3. #3
    Registered User
    Join Date
    05-13-2013
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Conditional Formatting based off a another Tab

    I corrected the sheet names but I get the following error:

    Error.gif

    This is the formula I used:

    =ABS('4_30_13'!$I$5-'5_7_13'!$I$5) > =10%

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional Formatting based off a another Tab

    It's the spaces, around the > symbol.
    I typed it that way because the board software sometimes truncates a formula with the < > symbols because it thinks they are HTML code tags.
    So just remove those spaces.

  5. #5
    Registered User
    Join Date
    05-13-2013
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Conditional Formatting based off a another Tab

    When I use this formula it colors them all the same color and I know that there is only one of the items that is over 10% difference and it is a deficit of over 10%....

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional Formatting based off a another Tab

    OK, you'll need to get more specific...

    10% (of what?)
    do you mean if the current week's number is more than 10% different from the previous weeks number?

    Try
    =ABS('4_30_13'!$I$5-'5_7_13'!$I$5) > =('4_30_13'!$I$5*10%)

    Can you post a sample book ?

  7. #7
    Registered User
    Join Date
    05-13-2013
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Conditional Formatting based off a another Tab

    That WORKED!!!! That is exactly what I was needing. I needed to add (sheet/cell*10%). I was only leaving the 10% there and that is it. Now my question though is if it is -10% or lower then turn red and if +10% or greater then turn Green.

  8. #8
    Registered User
    Join Date
    05-13-2013
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Conditional Formatting based off a another Tab

    I think I figured it out! I took out the ABS function and just wrote typed it and it worked:

    for decrease of 10%
    =('4_30_13'!I1-I1)>=('4_30_13'!I1*10%)

    For Increase of 10% I used
    =('4_30_13'!I1-I1)<-('4_30_13'!I1*10%)

    This seemed to work for me!

  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional Formatting based off a another Tab

    Good deal, glad to help out.

  10. #10
    Registered User
    Join Date
    05-13-2013
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Conditional Formatting based off a another Tab

    You were aGREAT thanks! Sometimes you just need to bounce ideas around!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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