Conditionally format based off multiple calculations

[CthulhuNZ]

Hi,

I have a sheet essentially as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]10
[/TD]
[TD]6
[/TD]
[TD]8
[/TD]
[TD]0
[/TD]
[TD]12
[/TD]
[TD]40
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]Order 1
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 3
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]12
[/TD]
[TD][/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]10
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 4
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]6
[/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]


I'd like to conditionally format each cell in column A based off if any of the quantities in the same row exceed the quantity above them on row 1. In this example Order 1 and 3 would be conditionally formatted but not order 2 and 4.

I'd like to avoid using a macro if possible so it will update in real time as orders are added.

Apologies if this has been asked or answered elsewhere but I could not find something similar by searching.

Regards,
Ashley

 

[jtakw]

Hi,

I believe this will do it, use the formula indicated in my sample A7 for your CF formula rule, change/adjust cell references to suit your data:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1		1	2	10	6	8	0	12	40	6
2	Order 1	2	1						12
3	Order 2					1
4	Order 3	1		12		5	3		10
5	Order 4	1			2			6		1
6
7	1
Sheet3

Cell Formulas
Range	Formula
A7	=SUMPRODUCT(--(B$1:J$1))

 

[CthulhuNZ]

Thanks jtakw - That worked a charm, appreciate the help

 

[jtakw]

You're welcome, welcome to the forum.

 

[CthulhuNZ]

Hi,


I have a similar situation which I am stumped on. Example as follows:


Material
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]10
[/TD]
[TD]0
[/TD]
[TD]-2
[/TD]
[TD]5
[/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]4
[/TD]
[TD]-1
[/TD]
[/TR]
[TR]
[TD]Stock
[/TD]
[TD]50
[/TD]
[TD]20
[/TD]
[TD]8
[/TD]
[TD]10
[/TD]
[TD]5
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]8
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Order 1
[/TD]
[TD]-20
[/TD]
[TD][/TD]
[TD]-5
[/TD]
[TD][/TD]
[TD]-1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]-1
[/TD]
[/TR]
[TR]
[TD]Order 2
[/TD]
[TD]-20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]-1
[/TD]
[/TR]
[TR]
[TD]Order 3
[/TD]
[TD][/TD]
[TD]-20
[/TD]
[TD][/TD]
[TD]-5
[/TD]
[TD][/TD]
[TD]-1
[/TD]
[TD]-1
[/TD]
[TD]-4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
- These cells are a sum of the column below










Looking for a conditional formula which highlights an order (Column A) if it is using any of the material in which row A is a negative number (this order plus other orders would cause stock to drop into negative quantities)

In this example Order 1, 2 and 4 would highlight as they are all part of the sum which causes a negative quantity in D1.

Again trying to avoid VBA so it can update in real time - I've played around with arrays and sumproducts but cannot seem to get it to work.

Any help would be appreciated.

Regards,
Ashley

 

[Marcelo Branco]

Select A3:A6

In Conditional Formatting use this formula
=COUNTIFS($B3:$J3,"<0",$B$1:$J$1,"<0")
pick the format you want.

Hope this helps

M.

 

[CthulhuNZ]

Of course! So simple - I was definitely over thinking it.

Thank you for your help Marcelo

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.