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

1. ## 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

2. ## 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

3. ## 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. ## 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:
`Please Login or Register  to view this content.`

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

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

Let us know if you have any questions.

5. ## 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. ## 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. ## 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. ## 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.

9. ## 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. ## 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.

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

#### 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