Conditional formatting (or some other method)...

wardnine

Board Regular
Joined
Feb 27, 2004
Messages
56
I have this table:
sampletable.xls
ABCDEFGHIJKL
4STATEREVENUESEXPENDITURES
5
6TOTALFEDERALSTATELOCALTOTALCURRENTCAPITALOTHERDEBTASSETS
7SPENDINGOUTLAY
8
9United States................................................................6,912,961,561577,851,9863,384,155,7082,950,953,8677,120,866,0016,120,917,568792,997,957206,950,4763,953,662,9091,993,337,830
10
11Alabama........................................................................81,541,9858,912,95346,581,60226,047,43083,152,31874,782,7906,786,7801,582,74835,611,35221,933,387
12Alaska...........................................................................22,387,3844,072,94112,769,9255,544,51825,281,53720,932,6843,877,979470,87410,764,564-
13Arizona...........................................................................101,822,08311,627,09045,728,27544,466,71897,501,50679,741,24213,574,8794,185,38563,671,14625,183,287
14Arkansas........................................................................50,684,4515,965,16837,591,0757,128,20850,721,47245,719,6253,881,7471,120,10127,844,45217,986,140
15California........................................................................910,115,23190,991,784527,798,926291,324,521901,331,175772,186,053115,262,19413,882,929383,017,122418,888,262
16
17Colorado.........................................................................98,311,9406,283,43842,628,93849,399,563104,315,58987,213,28012,891,7254,210,58385,991,16144,575,911
18Connecticut....................................................................107,348,5155,510,71638,965,84662,871,953103,711,62593,028,7808,475,9592,206,88634,875,054495,775
19Delaware........................................................................18,960,8901,444,18012,473,2105,043,49920,067,26917,192,3792,688,578186,3125,125,5951,562,590
20District of Columbia..........................................................17,490,7582,395,804-15,094,95317,363,61914,313,4553,050,165---
21Florida............................................................................306,875,68530,721,807136,419,514139,734,365316,828,229263,541,81745,908,9047,377,509159,182,53692,642,042
1


This table actually includes data for all 50 states but it is too large to be posted on the board in its entirety. I'm basically looking to create a series of add-check edits within the table, but not exactly sure of the easiest way to set this up. There are three edits I want to set up:

1) the sum of federal, state, and local revenues in columns d, e, and f should be within 1 of the total revenues in column c. If not within 1, I wanted the total in column c to be shaded red

2) the sum of current spending, capital outlay, and other expenditures in columns h, i, and j should be within 1 of the total expenditures in column g.
If not within 1, I wanted the total in column g to be shaded red.

3) the sum of data for all the states from rows 11-70 (all rows are obviously not shown in the partial table I have above due to size restrictions on this board) should be within 1 of the u.s. total in row 9. Each column here needs to be checked individually. If not, I wanted the u.s. total in row 9 to be shaded red.

Is it possible to set this up without going through and typing in conditional formatting code for each individual cell? There are about 15 other tables in addition to this one where I need to do something very similar (though not exactly the same) so was looking to see if I could somehow copy and paste the conditional fomatting formulas to other cells without changing the actual values or border formats of the other cells? Is there an easier way to do this without using conditional formatting for cells?

Any advice is appreciated, thanks!!!

Julie
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

wwbwb

Well-known Member
Joined
Oct 20, 2003
Messages
513
for #1, highlight c11:c70 and apply a conditional formating of
Code:
=$c11<$d11+$e11+$f11-1
=$c11>$d11+$e11+$f11+1

for #2, hightlight g11:g70 and apply a conditional formating of
Code:
=$g11<$h11+$i11+$j11-1
=$g11>$h11+$i11+$j11+1

for #3, highlight c9 and apply a conditional formatting of
Code:
=c9<sum(c11:c70)-1
=c9>sum(c11:c70)+1
 

Watch MrExcel Video

Forum statistics

Threads
1,118,069
Messages
5,570,008
Members
412,304
Latest member
citrus
Top