Conditional Action

[milesy]

HI all

something which shouldnt be difficult but is alluding me

Excel sheet

A B C D E
1 C001 100 10 0 1000
2 C004 150 15 80 8000
3 C005 203 100 200 50
4 C006 207 60 30 150

So column A is a part number, B and C and D are qtys allocated to a job and E is the stock on hand

i want t take B1 and say D1-B1>0 therefore turn B1 Green

Then C1 needs to say D1-sum(B1:C1) >0 then turn C1 green - however if this was not true turn it red??

Should be simple except the job columns can be numerous

Anyone got a simple solution they can help with??

 

[etaf]

Should be simple except the job columns can be numerous

if the Jobs are numerous , does this mean the Stock on hand column changes based on the number of Jobs - so is not always column E

The formula for conditional formatting should , as you say, be simple
but what you are trying to do , may be the issue

B, C & D are just qty for jobs

so D1 - B1 , is just 2 random jobs, dont understand the relations ship

If you are looking to see if you have enough stock on hand to complete the jobs , then i would expect E to be used

can you explain what D1-B1 > 0 and D1-(B1+C1) > 0 actually gives you , may help understand your data

you can use conditional formatting
Highlight column B
then use a formula
= $D1-$B1 > 0
format for green

new rule
Highlight column C
then use a formula
= $D1-sum($B1:$C1) > 0
format for green

new rule
Highlight column C
= $D1-sum($B1:$C1) <= 0
format red

 

[milesy]

if the Jobs are numerous , does this mean the Stock on hand column changes based on the number of Jobs - so is not always column E

The formula for conditional formatting should , as you say, be simple
but what you are trying to do , may be the issue

B, C & D are just qty for jobs

so D1 - B1 , is just 2 random jobs, dont understand the relations ship

If you are looking to see if you have enough stock on hand to complete the jobs , then i would expect E to be used

can you explain what D1-B1 > 0 and D1-(B1+C1) > 0 actually gives you , may help understand your data

you can use conditional formatting
Highlight column B
then use a formula
= $D1-$B1 > 0
format for green

new rule
Highlight column C
then use a formula
= $D1-sum($B1:$C1) > 0
format for green

new rule
Highlight column C
= $D1-sum($B1:$C1) <= 0
format red

HI Wayne

so the SOH value is a constant for a given report and does not change based on the number of jobs

the jobs can be numerous so there can be anywhere from C to G (in otherwords anywhere from 1 to 10 or more jobs)


what i really need is something along the lines of

E1 - B1 > 0 (turn green) (column B)
E1-(B1+C1) > 0 (turn green) (column C)
E1-(b1+c1+d1) >0 (turn green) (column D)


but without having to enter each calculation manually as this would be quite time consuming

an ideal solution would be a VB script where you can input the columns for the Jobs and then it automatically adds the conditional formatting

 

[petertenthije]

Would it be feasible for you to move the SOH column between part number and job 1?

If this is possible then you can place the following formula within the conditional formatting screen on cell C1 (job 1, row 1). This formatting can then be pasted using format painter into each applicable column and row. The result of this formatting would be a green cell, the regular layout can be made red. You could even consider adding amber for jobs with partial stock on hand.

=SUM($C1:C1)<$B1

 

[milesy]

Would it be feasible for you to move the SOH column between part number and job 1?

If this is possible then you can place the following formula within the conditional formatting screen on cell C1 (job 1, row 1). This formatting can then be pasted using format painter into each applicable column and row. The result of this formatting would be a green cell, the regular layout can be made red. You could even consider adding amber for jobs with partial stock on hand.

=SUM($C1:C1)<$B1

yes i can mve the SOH column

i tried this but painting the conidtional formatting doesnt seem to extend the series (as in the formula above remains as SUM($c1:C1) on the pasted cells?

 

[petertenthije]

as in the formula above remains as SUM($c1:C1) on the pasted cells?

Are you sure the dollar signs are placed correctly?

 

[milesy]

Are you sure the dollar signs are placed correctly?

appologies got it working thanks

additional question though some of the cells are blank - can i add an if statement so the conditional formatting only occours if there is a value in the cell?

 

[petertenthije]

additional question though some of the cells are blank - can i add an if statement so the conditional formatting only occours if there is a value in the cell?

You can have as many conditional formats as you want.

Rather then using a format based on a formula, you can use the conditional formatting option "format only cells that contain" and then select BLANKS. For the formatting you can use a white background, since, assuming you used my original proposal, the basic background is already red.

Make sure the "BLANKS" format is listed above the GREEN format.