formula that writes to a *different* cell?

[john]

I have 2 columns, a and b, as follows -

a1 0
a2 1
a3 text
a4 10
a5 more text
a6 4
a7 3
a8 23
a9 1
a10 3


b1
b2
b3
b4 12
b5
b6
b7
b8 25
b9
b10


What I want to do is replace the contents of A with the contents of B if it
exists, otherwise leave it as-is.

eg.
a1 0
a2 1
a3 text
a4 12
a5 more text
a6 4
a7 3
a8 25
a9 1
a10 3


How can I do this?

Is there a way for a formula to write to a different cell? eg. C1 -->
=if(b1<>"","",a1=b1)

ps. the columns are 60600 rows long, and the numbers in B are at irregular
intervals.

 

[Roger Govier]

Hi John

You cannot use a formula to force a change.
Instead, in C1
=IF(B1="",A1,B1)
Copy down by double clicking the fill handle (the small black cross that
appears as you hover over the bottom left corner of C1)
Then, copy the whole of column C, and move cursor to cell A1 >Paste
Special>Values

 

[john]

duh! so obvious. thanks, that does what I wanted.