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

Mark,

Label your column of names with a title (say Names)
The names you filled are now in A2:A5

Select the range of names (and maybe a few addional rows.
Data / Form ; Click OKbutton.
On the inputform now click New record and type Anne and push the Enter key

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"Mark Flaxman" wrote in message
...
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

Hi m8,

Thanks for your advice.

I had tried Data / Form / New but I don't think this is
the answer I need.

I have now "named" the range A2 to A10 with a name, and
tried Data / Form again, but it's not working for me. It
is probably me, but I am not sure I understand your
solution to my problem.

I need to capture what has been input by someone else
elsewhere on the form. Data / Form doesn't seem to be
able to capture from a different cell reference.

Bu thanks anyway for your time and advice

Mark Flaxman

-----Original Message-----
Mark,

Label your column of names with a title (say Names)
The names you filled are now in A2:A5

Select the range of names (and maybe a few addional rows.
Data / Form ; Click OKbutton.
On the inputform now click New record and type Anne and
push the Enter key

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"Mark Flaxman" wrote in
message
...
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


.

Mark,

I don't think that you can do what you want to do ( append to a range the
value of a *cell* elsewhere on the sheet) other than by VBA.

The solution I gave (and that the reason why i gave it) does this, although
the input then is not from a cell, but via a form. I'l keep an eye on it to
watch if someone else does come with a solution.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"Mark Flaxman" wrote in message
...

Hi m8,

Thanks for your advice.

I had tried Data / Form / New but I don't think this is
the answer I need.

I have now "named" the range A2 to A10 with a name, and
tried Data / Form again, but it's not working for me. It
is probably me, but I am not sure I understand your
solution to my problem.

I need to capture what has been input by someone else
elsewhere on the form. Data / Form doesn't seem to be
able to capture from a different cell reference.

Bu thanks anyway for your time and advice

Mark Flaxman

-----Original Message-----
Mark,

Label your column of names with a title (say Names)
The names you filled are now in A2:A5

Select the range of names (and maybe a few addional rows.
Data / Form ; Click OKbutton.
On the inputform now click New record and type Anne and
push the Enter key

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"Mark Flaxman" wrote in
message
...
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


.

Yes I think you're right, it is not possible without
using VBA.

I got your Data / Forms method to work. It "was" me who
was wrong. I didn't understand what it was doing, but
Debra's web-site in the replies above these had a link
which pointed me to the right way.

So, I can now enter data in the next empty cell, (or
using a 2D table range, I can input data in the next
empty row), But it isn't quite what I require. I'm almost
there.

I don't trust users to be able to find Data / Forms,
either from the menu or using shortcut keys, and then to
press "New", without making an error and corrupting the
worksheet. I am not belittling the users, it is just that
they are not comfortable with spreadsheets,and I wanted
to keep the process as simple as possible.

I need to create a macro button that, when they are ready
to enter a new record, they press the button, and the
Data / Form dialogue appears, with "New" already selected.

Macro's don't work, because I can't "stop" recording a
macro with the Form dialogue box open.

How else can I do this?

I appreciate all the previous advice. It is getting me
there, :)

I am willing to tackle a VBA solution if there is one, :(

Mark Flaxman

-----Original Message-----
Mark,

I don't think that you can do what you want to do (
append to a range the
value of a *cell* elsewhere on the sheet) other than by
VBA.

The solution I gave (and that the reason why i gave it)
does this, although
the input then is not from a cell, but via a form. I'l
keep an eye on it to
watch if someone else does come with a solution.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"Mark Flaxman" wrote in
message
...

The following code will open the data form with a new record selected.
Change the sheet name and cell reference to match your worksheet.

'==============================
Sub OpenDataForm()
Dim ws As Worksheet
Set ws = Worksheets("Data")

SendKeys "%w"
ws.Range("A1").Worksheet.ShowDataForm
End Sub
'======================

Mark Flaxman wrote:
Yes I think you're right, it is not possible without
using VBA.

I got your Data / Forms method to work. It "was" me who
was wrong. I didn't understand what it was doing, but
Debra's web-site in the replies above these had a link
which pointed me to the right way.

So, I can now enter data in the next empty cell, (or
using a 2D table range, I can input data in the next
empty row), But it isn't quite what I require. I'm almost
there.

I don't trust users to be able to find Data / Forms,
either from the menu or using shortcut keys, and then to
press "New", without making an error and corrupting the
worksheet. I am not belittling the users, it is just that
they are not comfortable with spreadsheets,and I wanted
to keep the process as simple as possible.

I need to create a macro button that, when they are ready
to enter a new record, they press the button, and the
Data / Form dialogue appears, with "New" already selected.

Macro's don't work, because I can't "stop" recording a
macro with the Form dialogue box open.

How else can I do this?

I appreciate all the previous advice. It is getting me
there, :)

I am willing to tackle a VBA solution if there is one, :(

Mark Flaxman


-----Original Message-----
Mark,

I don't think that you can do what you want to do (

append to a range the

value of a *cell* elsewhere on the sheet) other than by

VBA.

The solution I gave (and that the reason why i gave it)

does this, although

the input then is not from a cell, but via a form. I'l

keep an eye on it to

watch if someone else does come with a solution.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"Mark Flaxman" wrote in

message

...



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

There are instructions here for manually or programmatically filling
blank cells in a column:

http://www.contextures.com/xlDataEntry02.html

Mark Flaxman wrote:
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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra,

Many thanks for this. That web page has a link to another
which helped me a lot.

Thanks again

Mark Flaxman

-----Original Message-----
There are instructions here for manually or
programmatically filling
blank cells in a column:

http://www.contextures.com/xlDataEntry02.html


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.