Hi, how do I "push" a value into a cell?

What do I mean?

I know that if I have cell A1, I can make the contents of
that cell equal to the value of any other cell in a
workbook. The easiest way is to enter

the formula in cell A1, "=B1" (where B1 holds the
contents that I want A1 to hold). I can even make a
conditional formula, eg if A1 is empty, fill it

with the contents of B1, else leave it alone.

But, what if I have a range of cells, eg A1 to A9 that I
want to fill, but only from top down, and only if the
cell is empty and the previous cell in the

range is not? eg;

A1 = Fred
A2 = John
A3 = Mary
A4 = James
A5 = ""
A6 = ""
A7 = ""
A8 = ""
A9 = ""

I want A5, the next empty cell in the range, to hold the
name Anne, but not the remainder of the empty cells?

I can set up a flag, say in the range C1 to C9, so that
if any cells in the A1 to A9 range are empty the flag
cell shows "0", or if any are not, the

flag cell shows "1", or if the cell above is empty, the
flag cell shows "2", eg

0 = empty, 1 = not empty, 2 = empty but the cell above
is also empty

C1 = 1 (A1 is not empty)
C2 = 1 (A2 is not empty)
C3 = 1 (A3 is not empty)
C4 = 1 (A4 is not empty)
C5 = 0 (A5 is empty)
C6 = 2 (A6 is empty but so is the previous cell)
C7 = 2 (and so on)
C8 = 2
C9 = 2


This way, I can use the MATCH function to find out that
the next empty cell is in row 5.

But then, if a user inputs into cell D1 the name Anne,
how do I "push" that value from D1 into the next
available empty cell, A5, and not all the

other empty cells, A6 to A9?

I feel I should be using the INDIRECT function somehow,
but I cannot get my head around the problem.

Any guidance would be appreciated.

Er.... I would rather not use VBA if I can avoid it. My
brain is too small to start to learn yet another
programming style, :)

Many thanks,

Mark