How to automate this task

This task arises with historical stock prices - when a stock has had
splits.

We have columns A,B,C,D and E.

For each row in columns A,B,C and D, we want replace the old value in
that row by

New Value = Old value times (Value in column E /value in column D)

This would effectively replace Column D by Column E and scale colums A
B and C.

How can this task be automated (I assume it would need a macro) ?

Thansk for any help.

Yes you need a macro if you want to fully automate it and replace the old data. however it seems that column E is used for the new info, so maybe you could just set a formula that checks if there is a value in column E, and if so, then use that data rather than the original data.

ie =if(E1="",original calculation,revised calculation)

Matt

Mallycat wrote:
> Yes you need a macro if you want to fully automate it and replace the
> old data. however it seems that column E is used for the new info, so
> maybe you could just set a formula that checks if there is a value in
> column E, and if so, then use that data rather than the original data.
>
> ie =if(E1="",original calculation,revised calculation)
>
> Matt
>

yes; actually I thought of that - there is a "circular reference"
problem -

if in cell A1, I write "' = A1*E1/D1" excel doesn't like that. Is
there a way to get around that ?

Thanks.

>
> --
> Mallycat
> ------------------------------------------------------------------------
> Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
> View this thread: http://www.excelforum.com/showthread...hreadid=561276