+ Reply to Thread
Results 1 to 10 of 10

Change background color based on a gain, loss or void of sales over two months

  1. #1
    Registered User
    Join Date
    05-23-2019
    Location
    Santa Rosa, CA
    MS-Off Ver
    2016
    Posts
    6

    Change background color based on a gain, loss or void of sales over two months

    Hello! I am trying to figure out how to create a report that will give me current sales dollars and highlight whether I have gained distribution versus the prior period, lost distribution or have a void which would mean I have no distribution over two periods.

    GAIN = GAIN Distribution v Prior Period (Highlight Green)
    LOST = Lost Distribution v Prior Period (Highlight Yellow)
    VOID = No distribution for 2 consecutive periods (Highlight Pink)

    I can't figure out how to show the sales dollars and highlight the cell based on the above criteria. I created a pivot table and then on another worksheet used an index and match to determine whether I have a gain, loss or void but I can't figure out how to highlight the cells and this doesn't show me the current sales dollars. Can anyone help me with this? I would really appreciate it! Thank you very much!

    Jane
    Attached Files Attached Files

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,888

    Re: Change background color based on a gain, loss or void of sales over two months

    Hello and welcome, Jane

    In the attached version of your file I've added formulas in columns J:N that (presumably) show the dollar values you're looking for.

    I don't fully understand how you calculate your GAIN / LOSS to drive the yellow and green highlighting though.
    Perhaps you could explain a little more?

    BSB
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-23-2019
    Location
    Santa Rosa, CA
    MS-Off Ver
    2016
    Posts
    6

    Re: Change background color based on a gain, loss or void of sales over two months

    Hi Bob,

    Thank you for the reply! I can easily get the current dollar amount with just a simple pivot table but what I'm really looking for is a report that will show me the current month sales dollars with cells highlighted where I have a loss from the previous month to the current month, a gain from the previous month to the current month or a void where I have $0 dollars in both periods.

    If I have sales in the current month and $0 in the previous month that would be a GAIN in distribution so the cell would be highlighted in Green.
    If I have $0 sales in the current month and positive sales in the previous month that would be a LOSS in distribution so the cell would be highlighted in Yellow.
    If I have $0 sales in the current month and $0 in the previous month that would be a VOID in distribution so the cell would be highlighted in Pink.

    I would love to be able to accomplish this in a pivot table but I don't know how to do this. I couldn't figure out how to highlight the cells using a conditional format either.

    Jane

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,285

    Re: Change background color based on a gain, loss or void of sales over two months

    As modeled on the pivot table the rules are:
    For green:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For yellow:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For pink:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    05-23-2019
    Location
    Santa Rosa, CA
    MS-Off Ver
    2016
    Posts
    6

    Re: Change background color based on a gain, loss or void of sales over two months

    Hi JeteMC,

    This is VERY close! Only thing is I need to show ONLY the Current Month. This report is going to be pretty big and it's not necessary to see last months sales, just whether or not there is a loss, gain or void and the current months sales. Is this possible?

    I really really appreciate the help. This one has really stumped me.

    Thank you!

    Jane

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,285

    Re: Change background color based on a gain, loss or void of sales over two months

    Off the top of my head I would say select and hide the "Last Month" columns (E, G, I...). As you have probably already found out, filtering out the "Last Month" columns doesn't work. You might be able to record a macro or write VBA code that would hide and unhide the "Last Month" columns, and post #4 of the linked thread gives an example. Unfortunately I don't speak VBA, however you might get some help from a VBA contributor if you mark this thread as 'Solved' then open a new thread in the VBA forum that focuses on how to hide/unhide the "Last Month" columns.
    Let us know if you have any questions.

  7. #7
    Registered User
    Join Date
    05-23-2019
    Location
    Santa Rosa, CA
    MS-Off Ver
    2016
    Posts
    6

    Re: Change background color based on a gain, loss or void of sales over two months

    Hi JeteMC,

    I can't get the file to work when I replace the data with my real data. Is there something I need to do in order to get the getpivotdata to refresh or something?

    Thanks!
    Jane

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,285

    Re: Change background color based on a gain, loss or void of sales over two months

    Make sure the pivot table is situated the same in the real data, that is that the $ sign is in cell A5. If not the formulas will need to be adjusted accordingly. In the attached copy one of the formulas is pasted into cell N5. If you press the F2 key that cell will be in edit mode and highlight the cells that are being referenced. The formulas in the real pivot table should reference those same cells, i.e. both the ones under 'Sort Order' and the first 'Corporate Retail Name', 'Banner Name' and 'Store'.
    Let us know if you have any questions.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    05-23-2019
    Location
    Santa Rosa, CA
    MS-Off Ver
    2016
    Posts
    6

    Re: Change background color based on a gain, loss or void of sales over two months

    Hi JeteMC,

    Ok I figured it out. The formula you gave me had the wrong cell for Item UPC and Sort Order but I didn't notice it originally because the headers were the same. When I flipped them it worked perfectly!

    I can't tell you how happy I am. I really really appreciate this!!!

    Thank you!

    Jane

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,285

    Re: Change background color based on a gain, loss or void of sales over two months

    You're Welcome, sorry about mixing up the UPC and Sort Order references. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. change background color based on the value
    By jasond1992 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-01-2018, 01:02 PM
  2. [SOLVED] How to change background color based on first row value?
    By putritersenyum in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-18-2016, 05:54 AM
  3. [SOLVED] Change Background Color based on Value?
    By Jangorage in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-22-2014, 04:11 AM
  4. Change cell background color based on another cells background color
    By Queo in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 06-10-2014, 05:28 AM
  5. Change Background Color Based On Cell
    By radicalrom in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2013, 10:58 AM
  6. Change Background Color of Row Based on Cell Value
    By ashishashish8 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-21-2011, 02:51 PM
  7. change text color based on background color
    By great.bean in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-05-2009, 07:07 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