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

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

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

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

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

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