Advancing One row without enter

[furstwinhall]

I am trying to set up a gift certificate program for the company I work for.
Using Excel, I have one sheet called payment info. and one sheet called gift
certificate. I want the user to be able to simply type in the information on
"payment info." and have the gift certificate "fill itself out". The problem
comes the second time I want to fill one out, as the cells on the gift cert
page are pointing to the first row on the other page, not the second. Is
there a way to have the gift cert page automatically advance one row so that
it takes the data that is at the bottom of the payment info. page? Any help
would be appreciated.

 

[akphidelt]

If you could show the formula you are using I can better help you... but it
is possible to do what you are asking.

 

[furstwinhall]

I am not using a "formula" just text. It's as though I need one sheet to
pull the bottom value off of a list. Any ideas?

 

[Rick Rothstein \(MVP - VB\)]

You have to be using a formula on your "Gift Certificate" sheet, otherwise
how is it getting the text you typed in on the "Payment Info" sheet? Or are
you using a VBA macro or VBA event code to do this? I'll assume you are
using formulas on the "Gift Certificate" sheet for this response. You will
have to set this up for each field you are filling in on the "Gift
Certificate" sheet, so I will only show you one formula which you will need
to modify for your other fields. Let's assume that Column A on the "Payment
Info" sheet (I'll assume that is the sheet name for this example) contains
the name that will be placed in the name cell on the "Gift Certificate"
sheet. Put this formula in that name cell on the "Gift Certificate" sheet...

=LOOKUP(2,1/('Payment Info'!A1:A65535<>""),'Payment Info'!A:A)

This formula will display the last entry in Column A on the "Payment Info"
sheet. Just adjust the column designations for the other fields on the "Gift
Certificate" sheet.

Rick

 

[akphidelt]

Well, im not sure how you are pointing to any rows with out a formula but one
way to always get the last row in a list of data is using the Indirect
function. For example say you have data starting in Row. Type in the formula
anywhere on the page that goes

=Indirect("A"&Counta(A:A))

Whenever you add a new line of data to this it will automatically choose the
last row of inputed data. I could probably help you out more if I had more
information. But this is all I got!

 

[furstwinhall]

Rick,
PERFECTION! How did you do that? I would love to know what features you
used to come up with that. Thanks again.

 

[Rick Rothstein \(MVP - VB\)]

=LOOKUP(2,1/('Payment Info'!A1:A65535<>""),'Payment Info'!A:A)

First off, the structure of this formula is not original with me... many
people have posted it in the past. Let's look at the middle argument first.
The denominator of the fraction is what is called a logical expression... in
essence, it is the kind of expression you would put in an IF function in a
formula on the worksheet (or an If statement in VB). Of course, in A1:A65535
is a range and the division into 1 will produce a range of results LOOKUP is
designed to look through, not the single value an IF function would normally
take. Anyway, logical expressions return either TRUE or FALSE, but when used
in a mathematical expression, TRUE is automatically converted to 1 (on the
worksheet, -1 in VB) and FALSE to 0. So the result of dividing the range of
values into 1 is a range fractional results, either 1/1 or 1/0; but, note
that 1/0 produces a #DIV/0! error which means the range of results returned
from the division is a list of 1's or #DIV/0!. The LOOKUP function will
ignore the #DIV/0! values and look only at the 1's. However, the value we
are trying to find is 2. LOOKUP will, of course, not find this value in the
range of results. When it can't find the value it is looking for, it returns
the largest value less than the value. In this case, that is a 1... it turns
out it will use the last 1 in the list of matching 1's (which, of course,
corresponds to the last filled in cell in the range A1:A65535. Once it has
the offset from A1 for that last 1 (filled in cell), it will use that offset
to offset into the A:A, the third argument (since A:A starts at A1)... that
found value being the contents of the last filled in cell in Column A.

Rick