Conditional Formatting over multiple cells

[PetrockAmmoTech]

Hello everyone.

I'm generating a user worksheet that must keep our users from exceeding certain values over multiple line items.

Basically each item has a line, but multiple items can fall into the same "category" and when taken individually and or added together must not exceed a specific value.

So if lines 1,2,5 and 9, all added together, and/or if one of the lines alone, exceed a specific value, the numbers all turn red to warn the user that they've exceeded their qty available.

Any help would be greatly appreciated.

[Kaper]

Use a formula to apply conditional formatting
see for instance https://support.office.com/en-IE/art...b-f1951ff89d7f

[Pepe Le Mokko]

Please post a sample sheet. Are there any negative values?

[PetrockAmmoTech]

I cannot post a sample of the sheet I'm working on as the information is controlled. There are no negative values. Lowest value being zero and the highest value may be veriable depending on category code.

I'll do my best to fake a sheet.

[Kaper]

Indeed we do not want to see your delicate data. prepare a dummy file with few entries, and manually applied formatting. (short description why particular cell was/was not formatted in a comment or in next column could be helpfull).

[Pepe Le Mokko]

If the suim of one row exceeds the limit, so will sthe sum of all rows
So a condition like =sum(sum(a1:aa2),sum(a5:aa5),sum(a9:aa9))>your_limit as CF should do

Your_limit cab be hard-coded or a cell ref if needed

[PetrockAmmoTech]

I'm very new to using excel in this advanced manner and have no training with the program other than what I've managed to figure out.

I'll post the fake workbook that I've generated and I'll show what I'm trying to accomplish with statements in blue.

[PetrockAmmoTech]

Practice Book.xlsx

I hope this works.

I'm sorry I don't know more about the program, to make reaching a solution easier.

[Kaper]

Formula used to set the rule for conditional formatting (see in post #2 how to apply):

=SUMIF(A:A,A2,D:D)

see in attached file "in action"

[PetrockAmmoTech]

I don't know if I did something wrong, but I don't see any changed in the practice book. I tried changing the numbers, but nothing happened.

[Kaper]

I have no idea what you did (or what you changed) and if you were trying my attachment.

So please download (again ?)
Kopia Practice Book.xlsx
from post #8 and try such changes as marked on the picture below.

For instance, start with changing D2 to 90. Current blue interios for all cells where column A is 0001 shall disappear.

Then try other changes and see how blue appears in other cells.

Have a good weekend,

Kaper

[PetrockAmmoTech]

Ah I see! I still do not understand the formula and cannot successfully apply it too my sheet.

One thing that I did not consider, the category code is different though the items are similar. When I amend our practice book, the formula doesn't work. I've changed this practice book to better reflect my actual spreadsheet.

Is it possible that I may need to add a column and assign like items with a serial, like a number or letter? My serial column would be G and my item qty column is H.

Thanks for your help, thus far.

Kopia Practice Book 2.xlsx

[Kaper]

So for cells D2,3 and 7 (you can select them with Ctrl pressed) conditional formatting rule based on formula:

=sum($D$2;$D$3;$D$7)>2000

and for whole D2:Dx range formula

=D2>2000

(or standard conditional formatting (not based on formula) just greater than)

[PetrockAmmoTech]

I tried the formula =sum()>2000 but the cells did not change colour when data was inputed. I used the Cntl feature, as suggested.

I've also notice that I'm using version 2003, not 2010.

Here is my actual workbook with all of the sensitive info removed. As you can see, lines 3,5,10,13,14,20 and 35 are the same. The item identifying code is A in column G. The Qty would be in H. These lines, when added together can not exceed 2000.

Maybe my actual work book will make a difference.

Practice book 3.xls

[Kaper]

So we are going back to post #9 SUMIF does the work.

=SUMIF($G$3:$G$100,"A",$H$3:$H$100)>2000

To not select only cells in column H rows 3,5,10,13,14,20 and 35 it can be added into the formula of conditional formatting (applicable to whole H3:H100 range).

=AND(SUMIF($G$3:$G$100,"A",$H$3:$H$100)>2000,$G3="A")

[PetrockAmmoTech]

Well fellas, sorry for the delay in getting back to everyone.

I've tried all the suggestions, and have been successful in getting individual cells to turn red when exceeding the value, but none of the other cells that should turn, will.

Hopefully soon we'll get this sorted out.