Pivot Table Conditional Formatting

[TinaP]

Hi all!

I'm working on a project that keeps track of all the cash in a cash drawer. The end user would like all cash drawers with balances over $12000 highlighted. Supervisor drawers, which have drawer numbers ending in "00", should not have any highlighting. I've created pivot tables to display the information.

I've even managed to add the conditional formatting and entered the condition to not format drawers ending in "00". However, to add the special condition for the supervisor's drawer I had to "hard code" the row number.

=AND(RIGHT(B$7,2)<>"00",B8>12000)

If I keep the hard coding in, the highlighting rule could be compromised due to the flexibility of the pivot table. For instance, if a report filter is added or removed, the row with the drawer numbers in it will likely change and therefore the supervisor's drawer will not be found.

Here is a very small example of my sheet, but it shows all pertinent info:

Excel 2010
	A	B	C	D	E
1
2	BRANCH NAME	Farmersville		MAXIMUM ALLOWED	600,000
3	MONTHS	Jun
4	DAY OF WEEK	(All)
5
6	END AMT
7		100	101	102	103
8	1-Jun	303,076.00	12,423.42	9,876.22	2,382.81
9	2-Jun	330,476.00	9,179.34	6,896.18	2,382.81
10	3-Jun	296,701.00	7,399.80	6,896.18	2,382.81
11	6-Jun	321,458.00	12,254.16	9,009.87	2,382.81
12	7-Jun	273,678.00	11,578.19	7,904.77	2,382.81
PIVOT

Is there anything that can be done to resolve this?

Excel 2010

 

[Marcelo Branco]

Is the row that has the supervisors codes (100, 101, 102 ...) always immediately below the row that contains the text "END AMT" in column A?
If so, maybe this
=AND(RIGHT(INDEX(B:B,MATCH("END AMT",$A:$A,0)+1),2)<>"00",B8>12000)

Hope this helps

M.

 

[TinaP]

I believe "END AMT" will always be there, but things change. Your formula works great, though. I'm sure going forward, I'll be able to modify it according to whatever the end users request.

Thank you for all your help.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.