same cell formula 1

[leehale]

Hi. I have what is probably a very simple challenge to some out there.
I have a row of nubers. A1 is a lookup for a stock figure. B1 is the net req. C1 is the requirment. D1:H1 are shedule requirements by week.
what I would like to do is set the cells so that if for example the stock is 3000kgs, the requirement is 5000kgs then the net requirement will be 2000kgs.easy. Then I want the cells D1,E1,F1 to change to '0'. leaving G1,H1 showing a reqirment for 1000 each. the requirements are keyed in to the cells not looked up so if I thought a conditional format would work but cannot get it to work myself. I do not want to add more columns to an already big sheet that will be effected else where. any help to my ramblings will be most appreciated.

 

[bluedragon2]

OK, So by reading your question, I gather that you want the following:

A1 B1 C1 D1 E1 F1 G1 H1
5000 2000 3000 0 0 0 1000 1000

Now, here are my questions:

Are the data cells numbers like 5000 or are they like 5000kgs?

Is it always 1000 per week, or is there other factors that determine the weekly requiremnet?



Blue

If I wasn't Blue, I would just be a Dragon...

 

[leehale]

Hi. thanks for the reply. first there are no other symbols just numbers. the qty's of 1000 can and do vary as does the stock figure. the factors for the schedule requirements are from the customers weekly. I hope that is wha you needed.

 

[DaleWatson123321]

Hi leehale,

Here's a solution - based on my interpretation of what you're seeking...

This conditional formatting will result in the colors in columns D:H changing to:
GREEN if the total EQUALS the value in Column B.
YELLOW if the total is LESS THAN the value in Column B.
RED if the total is GREATER THAN the value in Column B.

Naturally you can change the colors to your liking.

Steps:

1) In cell D1, activate Conditional Formatting.

2) For Condition 1, change Cell Value Is to "Formula Is", and add this formula: =SUM($D1:$H1)=$B1

3) Click Format - then Pattern, and choose GREEN, then OK

4) Click "Add"

5) For Condition 2, change Cell Value Is to &quot;Formula Is&quot;, and add this formula: =SUM($D1:$H1)<$B1

6) Click Format - then Pattern, and choose YELLOW, then OK

7) Click &quot;Add&quot;

8) For Condition 3, change Cell Value Is to &quot;Formula Is&quot;, and add this formula: =SUM($D1:$H1)=$B1

9) Click Format - then Pattern, and choose GREEN, then OK

10) Copy cell D1,

11) Highlight the entire range you want to paste to. You could select the entire columns by clicking on the Column Letters (D:H)

12) Use: Edit - Paste Special - Formats. This way, you will copy the formatting, but leave any existing numbers intact.

I hope this is what you wanted. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

Hi again,

I was a &quot;little too hasty&quot;... #8 & #9 should have stated:

8) For Condition 3, change Cell Value Is to &quot;Formula Is&quot;, and add this formula: =SUM($D1:$H1)>$B1

9) Click Format - then Pattern, and choose RED, then OK

Hope this makes better sense now.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[bluedragon2]

To autopopulate your weekly requirements:

A1 is the stock: 3000
C1 is the req't: 5000
B1 is net: =C1-A1 (2000)

Since you said the weekly is determined by the customer, so I would ad a column for that amount lets say I1 and in this case 1000.

In cell H1, put:

=IF(B1=0,0,IF(B1-I1<0,B1,I1))

In cell G1, put:

=IF(H1<I1,0,IF(B1-(I1*2)<0,B1-I1,I1))

In cell F1, put:

=IF(G1<I1,0,IF(B1-(I1*3)<0,B1-(I1*2),I1))

In Cell E1, put:

=IF(F1<I1,0,IF(B1-(I1*4)<0,B1-(I1*3),I1))

In cell D1, put:

=IF(E1<I1,0,IF(B1-(I1*5)<0,B1-(I1*4),B1-(I1*5))



Blue

If I wasn't Blue, I would just be a Dragon...

 

[leehale]

I have tried to put this formula into a sheet to test and I cannot get it to populate the weeks. Am I doing this right? the conditional format was not what I was after. I was hoping that the formula would change numbers. your second option looked like a good solution. just can't get it to work.

 

[bluedragon2]

I can send you an example if you like...



Blue

If I wasn't Blue, I would just be a Dragon...

 

[leehale]

Yes please that would really helpful.

 

[bluedragon2]

I just need an email address



Blue

If I wasn't Blue, I would just be a Dragon...

 

[leehale]

good point. lee.hale@avon-rubber.com.

thanks

Lee