Automate Conditional Formatting

[DPChristman]

I have a large spreadsheet that consists of 250 Districts, and about 25 Zones.

On this spreadsheet I am capturing totals by District and Zone.

Based on an individual cell, can I set a conditional format (color) for that entire row.

Example below:
I want all my district totals to be shaded one color, and my zone totals shaded another color.

I know that I can do this by applying the data filter, show only rows with the word District in it, and shade accordingly. Then do the same with zones.

The problem is doing them row by row is painstakingly annoying, and if I just highlight the group of 250 district that I have filtered, the conditional format applies itself to the individual stores in the districts. Each of these stores has it's own conditional format based on their exceeding positive or negative thresholds.

What I want to do is shade the cells for DISTRICT and ZONE, without impacting the other cells. Anyone have any ideas?

	SALES #	SALES $	AVG SALE	RETURN #	RETURN $	AVG RET
DISTRICT 10	100	1,000.00	10.00	2	200.00	100.00
ZONE 01	600	6,000.00	10.00	12	1,200.00	100.00

<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[RCBricker]

I have a large spreadsheet that consists of 250 Districts, and about 25 Zones.

On this spreadsheet I am capturing totals by District and Zone.

Based on an individual cell, can I set a conditional format (color) for that entire row.

Example below:
I want all my district totals to be shaded one color, and my zone totals shaded another color.

I know that I can do this by applying the data filter, show only rows with the word District in it, and shade accordingly. Then do the same with zones.

The problem is doing them row by row is painstakingly annoying, and if I just highlight the group of 250 district that I have filtered, the conditional format applies itself to the individual stores in the districts. Each of these stores has it's own conditional format based on their exceeding positive or negative thresholds.

What I want to do is shade the cells for DISTRICT and ZONE, without impacting the other cells. Anyone have any ideas?

	SALES #	SALES $	AVG SALE	RETURN #	RETURN $	AVG RET
DISTRICT 10	100	1,000.00	10.00	2	200.00	100.00
ZONE 01	600	6,000.00	10.00	12	1,200.00	100.00

<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

select cell A2. You will be making all of your rules for this cell alone.

in the CD select new rule then select Use a Formula to determine which cells to format

in the box type the following

=$A2="District"

select your color. rinse and repeat the steps for each different color you want.

once you have all your rules. Use format Painter (little paint brush in the clipboard section of the home ribbon) and paint all the cells you want to follow your rules. the $A will ensure they check against Column A cell.

 

[DPChristman]

I think you lost me there.

I know what format painter is.

I don't understand what you mean my =$A2="District"

add the conditional format to the cells in a single row where only on cell contains the word district.

Doing a standard past doesn't work because it includes the threshold based formatting in the district totals. Since the combination of all stores in the district will exceed the threshold, it will always be highlighted (but shouldn't be)

 

[RCBricker]

I think you lost me there.

I know what format painter is.

I don't understand what you mean my =$A2="District"

add the conditional format to the cells in a single row where only on cell contains the word district.

Doing a standard past doesn't work because it includes the threshold based formatting in the district totals. Since the combination of all stores in the district will exceed the threshold, it will always be highlighted (but shouldn't be)

=$A2="District" is the formula you enter into the conditional formatting.

I am not sure we are on the same page with this. why would you not want all the stores with district in column A to be highlighted?

 

[DPChristman]

There are exception thresholds set for individual stores. Stores are highlighted when that store exceeds the thresholds. For example: average return threshold in the company may be $31.00. Any store in excess of that threshold is highlighted. That's the way it should work. However, if there are 4 stores in a district, and their combined returns exceed $31, that district would get highlighted as well. However, $31 for the district does not exceed the threshold. When we deal in percent to sales, it is not an issue if the threshold highlights or not because % to sales is percent to sales, but in unit counts or dollars, the cells are highlighted in error

Exception Threshold			($1,648.47)	-3.8%	15	$515.50	5.5%
295	$9,353.48	1,041.06	$42,826.36	($1,268.05)		12	$396.54	4.2%
861	861	861	861	861		860	860
253,722	$8,053,347.84	896,351	$36,873,496.41	($1,091,793.40)	-3.0%	10,120	$341,022.08	4.2%
CASH SALES	ALL SALES	DISCOUNTS	CASH RETURN INFORMATION
Sales #	Sales $	Sales #	Sales $	Total Discount Dollars	Percent To Sales	Cash Return #	Cash Return Dollars	% to Cash Sales $
394	$12,473.09	1,815	$77,814.96	($2,208.43)	-2.8%	13	$363.31	2.9%
526	$17,394.54	1,614	$62,901.19	($1,623.86)	-2.6%	25	$998.50	5.7%
361	$13,179.52	1,518	$70,751.42	($2,391.83)	-3.4%	10	$331.96	2.5%
406	$13,010.61	1,440	$57,331.87	($1,759.54)	-3.1%	18	$681.69	5.2%
1,687	56,057.76	6,387	268,799	($7,983.66)	-3.0%	66	2,375.46	4.2%
304	$7,589.37	1,193	$37,321.11	($742.74)	-2.0%	12	$217.24	2.9%
455	$14,657.09	1,523	$61,749.18	($2,750.81)	-4.5%	14	$535.87	3.7%
285	$8,115.46	890	$33,335.09	($1,077.04)	-3.2%	13	$392.65	4.8%
300	$9,751.61	1,051	$38,919.32	($1,346.96)	-3.5%	8	$303.77	3.1%
320	$8,052.19	1,293	$43,447.02	($1,332.78)	-3.1%	8	$188.23	2.3%
1,664	48,165.72	5,950	214,772	($7,250.33)	-3.4%	55	1,637.76	3.4%

<colgroup><col span="9"><col></colgroup><tbody>
</tbody>

 

[RCBricker]

There are exception thresholds set for individual stores. Stores are highlighted when that store exceeds the thresholds. For example: average return threshold in the company may be $31.00. Any store in excess of that threshold is highlighted. That's the way it should work. However, if there are 4 stores in a district, and their combined returns exceed $31, that district would get highlighted as well. However, $31 for the district does not exceed the threshold. When we deal in percent to sales, it is not an issue if the threshold highlights or not because % to sales is percent to sales, but in unit counts or dollars, the cells are highlighted in error

Exception Threshold			($1,648.47)	-3.8%	15	$515.50	5.5%
295	$9,353.48	1,041.06	$42,826.36	($1,268.05)		12	$396.54	4.2%
861	861	861	861	861		860	860
253,722	$8,053,347.84	896,351	$36,873,496.41	($1,091,793.40)	-3.0%	10,120	$341,022.08	4.2%
CASH SALES	ALL SALES	DISCOUNTS	CASH RETURN INFORMATION
Sales #	Sales $	Sales #	Sales $	Total Discount Dollars	Percent To Sales	Cash Return #	Cash Return Dollars	% to Cash Sales $
394	$12,473.09	1,815	$77,814.96	($2,208.43)	-2.8%	13	$363.31	2.9%
526	$17,394.54	1,614	$62,901.19	($1,623.86)	-2.6%	25	$998.50	5.7%
361	$13,179.52	1,518	$70,751.42	($2,391.83)	-3.4%	10	$331.96	2.5%
406	$13,010.61	1,440	$57,331.87	($1,759.54)	-3.1%	18	$681.69	5.2%
1,687	56,057.76	6,387	268,799	($7,983.66)	-3.0%	66	2,375.46	4.2%
304	$7,589.37	1,193	$37,321.11	($742.74)	-2.0%	12	$217.24	2.9%
455	$14,657.09	1,523	$61,749.18	($2,750.81)	-4.5%	14	$535.87	3.7%
285	$8,115.46	890	$33,335.09	($1,077.04)	-3.2%	13	$392.65	4.8%
300	$9,751.61	1,051	$38,919.32	($1,346.96)	-3.5%	8	$303.77	3.1%
320	$8,052.19	1,293	$43,447.02	($1,332.78)	-3.1%	8	$188.23	2.3%
1,664	48,165.72	5,950	214,772	($7,250.33)	-3.4%	55	1,637.76	3.4%

<colgroup><col span="9"><col></colgroup><tbody>
</tbody>

You are kinda going further out than your original post.

I might be able to help with these if you list all the times you want something colored.

So far we have District & Zone, each a different color

Now we want to add a new color (I assume for each) when a threshold is breached. How many thresholds are you planning on tracking by color?

regards