Conditionally format based off multiple calculations

[CthulhuNZ]

Hi,

I have a sheet essentially as follows:

	1	2	10	6	8	0	12	40	6
Order 1	2	1						12
Order 2					1
Order 3	1		12		5	3		10
Order 4	1			2			6		1

<tbody>
</tbody>

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

	10	0	-2	5	4	1	0	4	-1
Stock	50	20	8	10	5	2	1	8	1
Order 1	-20		-5		-1				-1
Order 2	-20								-1
Order 3		-20		-5		-1	-1	-4
Order 4			-5

<tbody>
</tbody>

- 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.