Conditional format within pivot table

[ajm]

Hi all. I wish to apply to different conditional formats to the one pivot table. First one operates only on the first data column and the second works on all subsequent data columns.

My data is plotting spend over a year for different offices. a single office may have multiple months of spend. The conditional format is to identify only the first month for each office.

For example,

Ascot has spend in July, Aug, and September but Morayfield only has spend in October. Another store, Ashfield, has spend in August and then again in November. I want the first month of recorded spend to be highlighted.

When I have used this method before, the spend data was calculated alongside of the pivot table., not within it. Two different conditional formats were used. The first was on the first column, July. for the whole column, if there was a value in that column greater than zero, fill that cell with RED.

Then from Aug (E) column to the June (N) column (Australian Fin Year is July to June):

IF(AND(SUM($E6:E6)=0,F6<>0),1,0) which tests column E (July) and F (Aug). If E =0 and F <> 0, apply 1. This would then be filled red and indicates that the first value for that office is in August

so how do i get both conditional formats into the pivot table and able to be refreshed as the data set improves.

 

[Sam_D_Ben]

Does this work,

=AND(E6>0,SUM(E6:E6)=E6)

 

[Peter_SSs]

I can't see your columns or rows, but why not something like

=AND(E6<>0,COUNT($E6:E6)=1)

@Sam_D_Ben
I'm pretty sure you meant =AND(E6>0,SUM($E6:E6)=E6) otherwise everything > 0 would be highlighted.

.. but even with this change it would fail with data like this

25 02 10.xlsm
	D	E	F	G	H
5		July	August	September	October
6	Ascot	3325	4656	6
7	other		4567		7564
8	wdawefqw	-5	5	10
First 2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E6:H8	Expression	=AND(E6>0,SUM($E6:E6)=E6)	text	NO

 

[Sam_D_Ben]

I can't see your columns or rows, but why not something like

=AND(E6<>0,COUNT($E6:E6)=1)

@Sam_D_Ben
I'm pretty sure you meant =AND(E6>0,SUM($E6:E6)=E6) otherwise everything > 0 would be highlighted.

.. but even with this change it would fail with data like this

25 02 10.xlsm
	D	E	F	G	H
5		July	August	September	October
6	Ascot	3325	4656	6
7	other		4567		7564
8	wdawefqw	-5	5	10
First 2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E6:H8	Expression	=AND(E6>0,SUM($E6:E6)=E6)	text	NO

Yeah! My bad

 

[Peter_SSs]

Yeah! My bad

Good idea to test your suggestions before posting where possible.

 

[ajm]

Thank you both. Peter, your solution works perfectly. like always. sorry about the missing image of my data. That's the second time the pic has not uploaded properly. I can see it here in the original post, exactly where i put it. So, it must be stored in a cached version or something technical like that. I'l try something different next time.

 

[Peter_SSs]

You're welcome. Glad it worked for you. Thanks for the confirmation.