Auomatically updatting formula when cell is not blank

[Raj]

Week 10 11 12 13 Forecast Balance Worst
case
Order book 75,261 91,423 83,455
70,585
Sales achieved 2,326 9,649 8,655 33,500 12,870

The Sales and order book has sheets for each month data and monthly sheet
has columns for weekly information.

The order book and sales achieved is updated at end of each week. Sales
forecast is entered at beginning of month. Balance is forecast less
achievement.

The worst case order book is week end order position less Balance . At the
end of 1st week c2, d2, e2 will be blank; at end of week 2 d2 & e2 will be
blank ....

As and when we enter each weeks information I am required to alter the
formula for Worst case order book from b2 to c2 to d 2 and so on. Is there
any way I can automate it -

Week end order position is updated manually as it is net of cancellations etc.


Thanks in advance

 

[Raj]

Thanks
Your assumptions are correct and this helps.

An obvious and a simple solution. Thanks again.

Raj

 

[OssieMac]

Hi Again Raj,

If my assumptions were correct in my previous post then you could copy the
following formula into the Worst case column:-

=IF(C2=0,B2-G2,IF(D2=0,C2-G2,IF(E2=0,D2-G2,E2-G2)))

The formula simply tests for the value in the next cell and if it = zero
then it uses the previous cell contents to perform the calculation. It
repeats this until the end of the 4 cells.

If this is not what you want then please answer the questions in my previous
post and I will have another look at it.

Regards,

OssieMac

 

[OssieMac]

Hi Raj,

I have been studying your question and I am not really sure that I know what
it is you want to do.

You say that Worst case Order book is week end order position less Balance.
Am I right to assume that you mean that each week you simply subtract the
balance from the last week end figure?

You have not got a Forecast or Balance shown for Order book line. It would
be easier to understand if I had these figures because I could simply test
the calculation. Please let me know what the Forecast and Balance figures are
for Order book line. Then tell me what formulas you would use for Worst case
for week 10 , week 11, week 12 and then I will have a look at it and see if
there is a way to solve your problem.

Regards,

OssieMac