software

I have a specific Application Form that I must fill out by hand at each
interview. I have created the form in Excel to allow me to type the info
into the appropriate fields. This however labor intensive and it is easy to
make a mistake that may change a different field.

Can I create separate listing or database (similar to the mail merge
concept) that will just drop the info into the proper cells on the form with
out me touching the form itself?

You could create an Input sheet. I'd name it Input lt;bggt;.

Then in column A, put a nice description. In column B, put your entry:

A B
FirstName Ziggy
LastName Stardust
Salutation Mr (or Ms.)
....
etc

Then on your Forms sheet, you'd use a formula that points back to that input
sheet.

=if(input!b1=quot;quot;,quot;quot;,input!b1)
(to grab the first name)

You could even protect the forms sheet so that you can't overwrite the formulas:
tools|protection|protect sheet

Mr. Ziggy wrote:
gt;
gt; I have a specific Application Form that I must fill out by hand at each
gt; interview. I have created the form in Excel to allow me to type the info
gt; into the appropriate fields. This however labor intensive and it is easy to
gt; make a mistake that may change a different field.
gt;
gt; Can I create separate listing or database (similar to the mail merge
gt; concept) that will just drop the info into the proper cells on the form with
gt; out me touching the form itself?

--

Dave Peterson

Fantastic... works great. I did modify the command to just read =Input!B1
because I didn't quite understand how to work the formula you gave me.

For example: =if(input!b1=quot;quot;,quot;quot;,input!b1). If I am only transfering
information, Why use an quot;ifquot; statement. When I put this in the form cell,
and put a sample information 1234 on cell b1 of the input page, I got a REF#
error.

I am assuming that I am supposed to have more information in the
=if(input!b1=quot;quot;,quot;quot;,input!b1) string... perhaps in the quotes? I'm just not
familiar enough with the formula.

Heres how I am translating it in my mind... If Cell B1 on the sheet titled
quot;Inputquot; equals... at this point I'm unsure what the quotes are to
respresent... then... again make the cell value... again not sure what the
quotes represent...if not, then input the information in Cell B1 of the
quot;Inputquot; sheet.

I'm also a bit embarrassed to admit the I am a little confused about the
lt;bggt; symbol you placed after the suggestion to name the input page Input lt;bggt;

Like I said the direct input formula works fine for what I'm doing right
now, I'm just afraid that I will run into a situation where your full formula
will be necessary.

Thanks so much for you assistance.

Mr. Ziggy

quot;Dave Petersonquot; wrote:

gt; You could create an Input sheet. I'd name it Input lt;bggt;.
gt;
gt; Then in column A, put a nice description. In column B, put your entry:
gt;
gt; A B
gt; FirstName Ziggy
gt; LastName Stardust
gt; Salutation Mr (or Ms.)
gt; ....
gt; etc
gt;
gt; Then on your Forms sheet, you'd use a formula that points back to that input
gt; sheet.
gt;
gt; =if(input!b1=quot;quot;,quot;quot;,input!b1)
gt; (to grab the first name)
gt;
gt; You could even protect the forms sheet so that you can't overwrite the formulas:
gt; tools|protection|protect sheet
gt;
gt;
gt;
gt;
gt; Mr. Ziggy wrote:
gt; gt;
gt; gt; I have a specific Application Form that I must fill out by hand at each
gt; gt; interview. I have created the form in Excel to allow me to type the info
gt; gt; into the appropriate fields. This however labor intensive and it is easy to
gt; gt; make a mistake that may change a different field.
gt; gt;
gt; gt; Can I create separate listing or database (similar to the mail merge
gt; gt; concept) that will just drop the info into the proper cells on the form with
gt; gt; out me touching the form itself?
gt;
gt; --
gt;
gt; Dave Peterson
gt;

=input!b1
will return what's in B1 of the Input sheet.

But if that cell is empty, you'll see a 0.

So
=if(input!b1=quot;quot;,quot;quot;,input!b1)
says to check what's in B1. If it's empty (quot;quot;), then show nothing (quot;quot;). But if
there's something there, show it instead.

I think you made a typo to get the #ref! error.

And lt;bggt; just mean big grin. I'd name the input sheet quot;Inputquot; was kind of a
feeble attempt at humor.Mr. Ziggy wrote:
gt;
gt; Fantastic... works great. I did modify the command to just read =Input!B1
gt; because I didn't quite understand how to work the formula you gave me.
gt;
gt; For example: =if(input!b1=quot;quot;,quot;quot;,input!b1). If I am only transfering
gt; information, Why use an quot;ifquot; statement. When I put this in the form cell,
gt; and put a sample information 1234 on cell b1 of the input page, I got a REF#
gt; error.
gt;
gt; I am assuming that I am supposed to have more information in the
gt; =if(input!b1=quot;quot;,quot;quot;,input!b1) string... perhaps in the quotes? I'm just not
gt; familiar enough with the formula.
gt;
gt; Heres how I am translating it in my mind... If Cell B1 on the sheet titled
gt; quot;Inputquot; equals... at this point I'm unsure what the quotes are to
gt; respresent... then... again make the cell value... again not sure what the
gt; quotes represent...if not, then input the information in Cell B1 of the
gt; quot;Inputquot; sheet.
gt;
gt; I'm also a bit embarrassed to admit the I am a little confused about the
gt; lt;bggt; symbol you placed after the suggestion to name the input page Input lt;bggt;
gt;
gt; Like I said the direct input formula works fine for what I'm doing right
gt; now, I'm just afraid that I will run into a situation where your full formula
gt; will be necessary.
gt;
gt; Thanks so much for you assistance.
gt;
gt; Mr. Ziggy
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; You could create an Input sheet. I'd name it Input lt;bggt;.
gt; gt;
gt; gt; Then in column A, put a nice description. In column B, put your entry:
gt; gt;
gt; gt; A B
gt; gt; FirstName Ziggy
gt; gt; LastName Stardust
gt; gt; Salutation Mr (or Ms.)
gt; gt; ....
gt; gt; etc
gt; gt;
gt; gt; Then on your Forms sheet, you'd use a formula that points back to that input
gt; gt; sheet.
gt; gt;
gt; gt; =if(input!b1=quot;quot;,quot;quot;,input!b1)
gt; gt; (to grab the first name)
gt; gt;
gt; gt; You could even protect the forms sheet so that you can't overwrite the formulas:
gt; gt; tools|protection|protect sheet
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; Mr. Ziggy wrote:
gt; gt; gt;
gt; gt; gt; I have a specific Application Form that I must fill out by hand at each
gt; gt; gt; interview. I have created the form in Excel to allow me to type the info
gt; gt; gt; into the appropriate fields. This however labor intensive and it is easy to
gt; gt; gt; make a mistake that may change a different field.
gt; gt; gt;
gt; gt; gt; Can I create separate listing or database (similar to the mail merge
gt; gt; gt; concept) that will just drop the info into the proper cells on the form with
gt; gt; gt; out me touching the form itself?
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

OMG!!!! lt;bggt; means quot;big grinquot;. Duh! LOL... sometimes I look at things too
deeply. I though there it be some techno. term I'm unfamiliar with. Perhaps
I need to send myself a (da) for dumbass. LOL

Thanks so much Dave for your help. That made things go so much smoother.
The next step is going to be finding a practical way to view the input page.
I'd love to be able to bring up individual data entry pages. Just type the
info for this section then hit the quot;nextquot; button and get the next section.
For now I just have the data fields set up in bordered cells all on one page.

quot;Dave Petersonquot; wrote:

gt; =input!b1
gt; will return what's in B1 of the Input sheet.
gt;
gt; But if that cell is empty, you'll see a 0.
gt;
gt; So
gt; =if(input!b1=quot;quot;,quot;quot;,input!b1)
gt; says to check what's in B1. If it's empty (quot;quot;), then show nothing (quot;quot;). But if
gt; there's something there, show it instead.
gt;
gt; I think you made a typo to get the #ref! error.
gt;
gt; And lt;bggt; just mean big grin. I'd name the input sheet quot;Inputquot; was kind of a
gt; feeble attempt at humor.
gt;
gt;
gt;
gt;
gt;
gt; Mr. Ziggy wrote:
gt; gt;
gt; gt; Fantastic... works great. I did modify the command to just read =Input!B1
gt; gt; because I didn't quite understand how to work the formula you gave me.
gt; gt;
gt; gt; For example: =if(input!b1=quot;quot;,quot;quot;,input!b1). If I am only transfering
gt; gt; information, Why use an quot;ifquot; statement. When I put this in the form cell,
gt; gt; and put a sample information 1234 on cell b1 of the input page, I got a REF#
gt; gt; error.
gt; gt;
gt; gt; I am assuming that I am supposed to have more information in the
gt; gt; =if(input!b1=quot;quot;,quot;quot;,input!b1) string... perhaps in the quotes? I'm just not
gt; gt; familiar enough with the formula.
gt; gt;
gt; gt; Heres how I am translating it in my mind... If Cell B1 on the sheet titled
gt; gt; quot;Inputquot; equals... at this point I'm unsure what the quotes are to
gt; gt; respresent... then... again make the cell value... again not sure what the
gt; gt; quotes represent...if not, then input the information in Cell B1 of the
gt; gt; quot;Inputquot; sheet.
gt; gt;
gt; gt; I'm also a bit embarrassed to admit the I am a little confused about the
gt; gt; lt;bggt; symbol you placed after the suggestion to name the input page Input lt;bggt;
gt; gt;
gt; gt; Like I said the direct input formula works fine for what I'm doing right
gt; gt; now, I'm just afraid that I will run into a situation where your full formula
gt; gt; will be necessary.
gt; gt;
gt; gt; Thanks so much for you assistance.
gt; gt;
gt; gt; Mr. Ziggy
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; You could create an Input sheet. I'd name it Input lt;bggt;.
gt; gt; gt;
gt; gt; gt; Then in column A, put a nice description. In column B, put your entry:
gt; gt; gt;
gt; gt; gt; A B
gt; gt; gt; FirstName Ziggy
gt; gt; gt; LastName Stardust
gt; gt; gt; Salutation Mr (or Ms.)
gt; gt; gt; ....
gt; gt; gt; etc
gt; gt; gt;
gt; gt; gt; Then on your Forms sheet, you'd use a formula that points back to that input
gt; gt; gt; sheet.
gt; gt; gt;
gt; gt; gt; =if(input!b1=quot;quot;,quot;quot;,input!b1)
gt; gt; gt; (to grab the first name)
gt; gt; gt;
gt; gt; gt; You could even protect the forms sheet so that you can't overwrite the formulas:
gt; gt; gt; tools|protection|protect sheet
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Mr. Ziggy wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; I have a specific Application Form that I must fill out by hand at each
gt; gt; gt; gt; interview. I have created the form in Excel to allow me to type the info
gt; gt; gt; gt; into the appropriate fields. This however labor intensive and it is easy to
gt; gt; gt; gt; make a mistake that may change a different field.
gt; gt; gt; gt;
gt; gt; gt; gt; Can I create separate listing or database (similar to the mail merge
gt; gt; gt; gt; concept) that will just drop the info into the proper cells on the form with
gt; gt; gt; gt; out me touching the form itself?
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

Someday, you may want to consider using a userform.

Debra Dalgleish has some get started instructions for userforms at:
contextures.com/xlUserForm01.html

Mr. Ziggy wrote:
gt;
gt; OMG!!!! lt;bggt; means quot;big grinquot;. Duh! LOL... sometimes I look at things too
gt; deeply. I though there it be some techno. term I'm unfamiliar with. Perhaps
gt; I need to send myself a (da) for dumbass. LOL
gt;
gt; Thanks so much Dave for your help. That made things go so much smoother.
gt; The next step is going to be finding a practical way to view the input page.
gt; I'd love to be able to bring up individual data entry pages. Just type the
gt; info for this section then hit the quot;nextquot; button and get the next section.
gt; For now I just have the data fields set up in bordered cells all on one page.
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; =input!b1
gt; gt; will return what's in B1 of the Input sheet.
gt; gt;
gt; gt; But if that cell is empty, you'll see a 0.
gt; gt;
gt; gt; So
gt; gt; =if(input!b1=quot;quot;,quot;quot;,input!b1)
gt; gt; says to check what's in B1. If it's empty (quot;quot;), then show nothing (quot;quot;). But if
gt; gt; there's something there, show it instead.
gt; gt;
gt; gt; I think you made a typo to get the #ref! error.
gt; gt;
gt; gt; And lt;bggt; just mean big grin. I'd name the input sheet quot;Inputquot; was kind of a
gt; gt; feeble attempt at humor.
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; Mr. Ziggy wrote:
gt; gt; gt;
gt; gt; gt; Fantastic... works great. I did modify the command to just read =Input!B1
gt; gt; gt; because I didn't quite understand how to work the formula you gave me.
gt; gt; gt;
gt; gt; gt; For example: =if(input!b1=quot;quot;,quot;quot;,input!b1). If I am only transfering
gt; gt; gt; information, Why use an quot;ifquot; statement. When I put this in the form cell,
gt; gt; gt; and put a sample information 1234 on cell b1 of the input page, I got a REF#
gt; gt; gt; error.
gt; gt; gt;
gt; gt; gt; I am assuming that I am supposed to have more information in the
gt; gt; gt; =if(input!b1=quot;quot;,quot;quot;,input!b1) string... perhaps in the quotes? I'm just not
gt; gt; gt; familiar enough with the formula.
gt; gt; gt;
gt; gt; gt; Heres how I am translating it in my mind... If Cell B1 on the sheet titled
gt; gt; gt; quot;Inputquot; equals... at this point I'm unsure what the quotes are to
gt; gt; gt; respresent... then... again make the cell value... again not sure what the
gt; gt; gt; quotes represent...if not, then input the information in Cell B1 of the
gt; gt; gt; quot;Inputquot; sheet.
gt; gt; gt;
gt; gt; gt; I'm also a bit embarrassed to admit the I am a little confused about the
gt; gt; gt; lt;bggt; symbol you placed after the suggestion to name the input page Input lt;bggt;
gt; gt; gt;
gt; gt; gt; Like I said the direct input formula works fine for what I'm doing right
gt; gt; gt; now, I'm just afraid that I will run into a situation where your full formula
gt; gt; gt; will be necessary.
gt; gt; gt;
gt; gt; gt; Thanks so much for you assistance.
gt; gt; gt;
gt; gt; gt; Mr. Ziggy
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; You could create an Input sheet. I'd name it Input lt;bggt;.
gt; gt; gt; gt;
gt; gt; gt; gt; Then in column A, put a nice description. In column B, put your entry:
gt; gt; gt; gt;
gt; gt; gt; gt; A B
gt; gt; gt; gt; FirstName Ziggy
gt; gt; gt; gt; LastName Stardust
gt; gt; gt; gt; Salutation Mr (or Ms.)
gt; gt; gt; gt; ....
gt; gt; gt; gt; etc
gt; gt; gt; gt;
gt; gt; gt; gt; Then on your Forms sheet, you'd use a formula that points back to that input
gt; gt; gt; gt; sheet.
gt; gt; gt; gt;
gt; gt; gt; gt; =if(input!b1=quot;quot;,quot;quot;,input!b1)
gt; gt; gt; gt; (to grab the first name)
gt; gt; gt; gt;
gt; gt; gt; gt; You could even protect the forms sheet so that you can't overwrite the formulas:
gt; gt; gt; gt; tools|protection|protect sheet
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Mr. Ziggy wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I have a specific Application Form that I must fill out by hand at each
gt; gt; gt; gt; gt; interview. I have created the form in Excel to allow me to type the info
gt; gt; gt; gt; gt; into the appropriate fields. This however labor intensive and it is easy to
gt; gt; gt; gt; gt; make a mistake that may change a different field.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Can I create separate listing or database (similar to the mail merge
gt; gt; gt; gt; gt; concept) that will just drop the info into the proper cells on the form with
gt; gt; gt; gt; gt; out me touching the form itself?
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson



quot;Dave Petersonquot; wrote:

gt; Someday, you may want to consider using a userform.
gt;
gt; Debra Dalgleish has some get started instructions for userforms at:
gt; contextures.com/xlUserForm01.html
gt;
gt; Mr. Ziggy wrote:
gt; gt;
gt; gt; OMG!!!! lt;bggt; means quot;big grinquot;. Duh! LOL... sometimes I look at things too
gt; gt; deeply. I though there it be some techno. term I'm unfamiliar with. Perhaps
gt; gt; I need to send myself a (da) for dumbass. LOL
gt; gt;
gt; gt; Thanks so much Dave for your help. That made things go so much smoother.
gt; gt; The next step is going to be finding a practical way to view the input page.
gt; gt; I'd love to be able to bring up individual data entry pages. Just type the
gt; gt; info for this section then hit the quot;nextquot; button and get the next section.
gt; gt; For now I just have the data fields set up in bordered cells all on one page.
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; =input!b1
gt; gt; gt; will return what's in B1 of the Input sheet.
gt; gt; gt;
gt; gt; gt; But if that cell is empty, you'll see a 0.
gt; gt; gt;
gt; gt; gt; So
gt; gt; gt; =if(input!b1=quot;quot;,quot;quot;,input!b1)
gt; gt; gt; says to check what's in B1. If it's empty (quot;quot;), then show nothing (quot;quot;). But if
gt; gt; gt; there's something there, show it instead.
gt; gt; gt;
gt; gt; gt; I think you made a typo to get the #ref! error.
gt; gt; gt;
gt; gt; gt; And lt;bggt; just mean big grin. I'd name the input sheet quot;Inputquot; was kind of a
gt; gt; gt; feeble attempt at humor.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Mr. Ziggy wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; Fantastic... works great. I did modify the command to just read =Input!B1
gt; gt; gt; gt; because I didn't quite understand how to work the formula you gave me.
gt; gt; gt; gt;
gt; gt; gt; gt; For example: =if(input!b1=quot;quot;,quot;quot;,input!b1). If I am only transfering
gt; gt; gt; gt; information, Why use an quot;ifquot; statement. When I put this in the form cell,
gt; gt; gt; gt; and put a sample information 1234 on cell b1 of the input page, I got a REF#
gt; gt; gt; gt; error.
gt; gt; gt; gt;
gt; gt; gt; gt; I am assuming that I am supposed to have more information in the
gt; gt; gt; gt; =if(input!b1=quot;quot;,quot;quot;,input!b1) string... perhaps in the quotes? I'm just not
gt; gt; gt; gt; familiar enough with the formula.
gt; gt; gt; gt;
gt; gt; gt; gt; Heres how I am translating it in my mind... If Cell B1 on the sheet titled
gt; gt; gt; gt; quot;Inputquot; equals... at this point I'm unsure what the quotes are to
gt; gt; gt; gt; respresent... then... again make the cell value... again not sure what the
gt; gt; gt; gt; quotes represent...if not, then input the information in Cell B1 of the
gt; gt; gt; gt; quot;Inputquot; sheet.
gt; gt; gt; gt;
gt; gt; gt; gt; I'm also a bit embarrassed to admit the I am a little confused about the
gt; gt; gt; gt; lt;bggt; symbol you placed after the suggestion to name the input page Input lt;bggt;
gt; gt; gt; gt;
gt; gt; gt; gt; Like I said the direct input formula works fine for what I'm doing right
gt; gt; gt; gt; now, I'm just afraid that I will run into a situation where your full formula
gt; gt; gt; gt; will be necessary.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks so much for you assistance.
gt; gt; gt; gt;
gt; gt; gt; gt; Mr. Ziggy
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; You could create an Input sheet. I'd name it Input lt;bggt;.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Then in column A, put a nice description. In column B, put your entry:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; A B
gt; gt; gt; gt; gt; FirstName Ziggy
gt; gt; gt; gt; gt; LastName Stardust
gt; gt; gt; gt; gt; Salutation Mr (or Ms.)
gt; gt; gt; gt; gt; ....
gt; gt; gt; gt; gt; etc
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Then on your Forms sheet, you'd use a formula that points back to that input
gt; gt; gt; gt; gt; sheet.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =if(input!b1=quot;quot;,quot;quot;,input!b1)
gt; gt; gt; gt; gt; (to grab the first name)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; You could even protect the forms sheet so that you can't overwrite the formulas:
gt; gt; gt; gt; gt; tools|protection|protect sheet
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Mr. Ziggy wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I have a specific Application Form that I must fill out by hand at each
gt; gt; gt; gt; gt; gt; interview. I have created the form in Excel to allow me to type the info
gt; gt; gt; gt; gt; gt; into the appropriate fields. This however labor intensive and it is easy to
gt; gt; gt; gt; gt; gt; make a mistake that may change a different field.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Can I create separate listing or database (similar to the mail merge
gt; gt; gt; gt; gt; gt; concept) that will just drop the info into the proper cells on the form with
gt; gt; gt; gt; gt; gt; out me touching the form itself?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

Dave:
My data is in Columns and I have at least 50 seperate quot;mergesquot; to do. Do I
have to change the reference for each form? i.e, the first form looks for
b1, the second form will need the data in b2, the third, b3, and so forth.
My data columns have First, Last etc.

A B C
1. First Last Address
2. Mary Smith 123 lane dirv
3. Larry Jones 456 Honeysuckle lane

Thanks for your help
Laura

quot;Dave Petersonquot; wrote:

gt; Someday, you may want to consider using a userform.
gt;
gt; Debra Dalgleish has some get started instructions for userforms at:
gt; contextures.com/xlUserForm01.html
gt;
gt; Mr. Ziggy wrote:
gt; gt;
gt; gt; OMG!!!! lt;bggt; means quot;big grinquot;. Duh! LOL... sometimes I look at things too
gt; gt; deeply. I though there it be some techno. term I'm unfamiliar with. Perhaps
gt; gt; I need to send myself a (da) for dumbass. LOL
gt; gt;
gt; gt; Thanks so much Dave for your help. That made things go so much smoother.
gt; gt; The next step is going to be finding a practical way to view the input page.
gt; gt; I'd love to be able to bring up individual data entry pages. Just type the
gt; gt; info for this section then hit the quot;nextquot; button and get the next section.
gt; gt; For now I just have the data fields set up in bordered cells all on one page.
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; =input!b1
gt; gt; gt; will return what's in B1 of the Input sheet.
gt; gt; gt;
gt; gt; gt; But if that cell is empty, you'll see a 0.
gt; gt; gt;
gt; gt; gt; So
gt; gt; gt; =if(input!b1=quot;quot;,quot;quot;,input!b1)
gt; gt; gt; says to check what's in B1. If it's empty (quot;quot;), then show nothing (quot;quot;). But if
gt; gt; gt; there's something there, show it instead.
gt; gt; gt;
gt; gt; gt; I think you made a typo to get the #ref! error.
gt; gt; gt;
gt; gt; gt; And lt;bggt; just mean big grin. I'd name the input sheet quot;Inputquot; was kind of a
gt; gt; gt; feeble attempt at humor.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Mr. Ziggy wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; Fantastic... works great. I did modify the command to just read =Input!B1
gt; gt; gt; gt; because I didn't quite understand how to work the formula you gave me.
gt; gt; gt; gt;
gt; gt; gt; gt; For example: =if(input!b1=quot;quot;,quot;quot;,input!b1). If I am only transfering
gt; gt; gt; gt; information, Why use an quot;ifquot; statement. When I put this in the form cell,
gt; gt; gt; gt; and put a sample information 1234 on cell b1 of the input page, I got a REF#
gt; gt; gt; gt; error.
gt; gt; gt; gt;
gt; gt; gt; gt; I am assuming that I am supposed to have more information in the
gt; gt; gt; gt; =if(input!b1=quot;quot;,quot;quot;,input!b1) string... perhaps in the quotes? I'm just not
gt; gt; gt; gt; familiar enough with the formula.
gt; gt; gt; gt;
gt; gt; gt; gt; Heres how I am translating it in my mind... If Cell B1 on the sheet titled
gt; gt; gt; gt; quot;Inputquot; equals... at this point I'm unsure what the quotes are to
gt; gt; gt; gt; respresent... then... again make the cell value... again not sure what the
gt; gt; gt; gt; quotes represent...if not, then input the information in Cell B1 of the
gt; gt; gt; gt; quot;Inputquot; sheet.
gt; gt; gt; gt;
gt; gt; gt; gt; I'm also a bit embarrassed to admit the I am a little confused about the
gt; gt; gt; gt; lt;bggt; symbol you placed after the suggestion to name the input page Input lt;bggt;
gt; gt; gt; gt;
gt; gt; gt; gt; Like I said the direct input formula works fine for what I'm doing right
gt; gt; gt; gt; now, I'm just afraid that I will run into a situation where your full formula
gt; gt; gt; gt; will be necessary.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks so much for you assistance.
gt; gt; gt; gt;
gt; gt; gt; gt; Mr. Ziggy
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; You could create an Input sheet. I'd name it Input lt;bggt;.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Then in column A, put a nice description. In column B, put your entry:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; A B
gt; gt; gt; gt; gt; FirstName Ziggy
gt; gt; gt; gt; gt; LastName Stardust
gt; gt; gt; gt; gt; Salutation Mr (or Ms.)
gt; gt; gt; gt; gt; ....
gt; gt; gt; gt; gt; etc
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Then on your Forms sheet, you'd use a formula that points back to that input
gt; gt; gt; gt; gt; sheet.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =if(input!b1=quot;quot;,quot;quot;,input!b1)
gt; gt; gt; gt; gt; (to grab the first name)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; You could even protect the forms sheet so that you can't overwrite the formulas:
gt; gt; gt; gt; gt; tools|protection|protect sheet
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Mr. Ziggy wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I have a specific Application Form that I must fill out by hand at each
gt; gt; gt; gt; gt; gt; interview. I have created the form in Excel to allow me to type the info
gt; gt; gt; gt; gt; gt; into the appropriate fields. This however labor intensive and it is easy to
gt; gt; gt; gt; gt; gt; make a mistake that may change a different field.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Can I create separate listing or database (similar to the mail merge
gt; gt; gt; gt; gt; gt; concept) that will just drop the info into the proper cells on the form with
gt; gt; gt; gt; gt; gt; out me touching the form itself?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

If you only had 32 fields, you could use Data|Form for data entry.

You may want to look at John Walkenbach's enhanced dataform:
j-walk.com/ss/dataform/index.htm
Laura Berry wrote:
gt;
gt; Dave:
gt; My data is in Columns and I have at least 50 seperate quot;mergesquot; to do. Do I
gt; have to change the reference for each form? i.e, the first form looks for
gt; b1, the second form will need the data in b2, the third, b3, and so forth.
gt; My data columns have First, Last etc.
gt;
gt; A B C
gt; 1. First Last Address
gt; 2. Mary Smith 123 lane dirv
gt; 3. Larry Jones 456 Honeysuckle lane
gt;
gt; Thanks for your help
gt; Laura
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; Someday, you may want to consider using a userform.
gt; gt;
gt; gt; Debra Dalgleish has some get started instructions for userforms at:
gt; gt; contextures.com/xlUserForm01.html
gt; gt;
gt; gt; Mr. Ziggy wrote:
gt; gt; gt;
gt; gt; gt; OMG!!!! lt;bggt; means quot;big grinquot;. Duh! LOL... sometimes I look at things too
gt; gt; gt; deeply. I though there it be some techno. term I'm unfamiliar with. Perhaps
gt; gt; gt; I need to send myself a (da) for dumbass. LOL
gt; gt; gt;
gt; gt; gt; Thanks so much Dave for your help. That made things go so much smoother.
gt; gt; gt; The next step is going to be finding a practical way to view the input page.
gt; gt; gt; I'd love to be able to bring up individual data entry pages. Just type the
gt; gt; gt; info for this section then hit the quot;nextquot; button and get the next section.
gt; gt; gt; For now I just have the data fields set up in bordered cells all on one page.
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; =input!b1
gt; gt; gt; gt; will return what's in B1 of the Input sheet.
gt; gt; gt; gt;
gt; gt; gt; gt; But if that cell is empty, you'll see a 0.
gt; gt; gt; gt;
gt; gt; gt; gt; So
gt; gt; gt; gt; =if(input!b1=quot;quot;,quot;quot;,input!b1)
gt; gt; gt; gt; says to check what's in B1. If it's empty (quot;quot;), then show nothing (quot;quot;). But if
gt; gt; gt; gt; there's something there, show it instead.
gt; gt; gt; gt;
gt; gt; gt; gt; I think you made a typo to get the #ref! error.
gt; gt; gt; gt;
gt; gt; gt; gt; And lt;bggt; just mean big grin. I'd name the input sheet quot;Inputquot; was kind of a
gt; gt; gt; gt; feeble attempt at humor.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Mr. Ziggy wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Fantastic... works great. I did modify the command to just read =Input!B1
gt; gt; gt; gt; gt; because I didn't quite understand how to work the formula you gave me.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; For example: =if(input!b1=quot;quot;,quot;quot;,input!b1). If I am only transfering
gt; gt; gt; gt; gt; information, Why use an quot;ifquot; statement. When I put this in the form cell,
gt; gt; gt; gt; gt; and put a sample information 1234 on cell b1 of the input page, I got a REF#
gt; gt; gt; gt; gt; error.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I am assuming that I am supposed to have more information in the
gt; gt; gt; gt; gt; =if(input!b1=quot;quot;,quot;quot;,input!b1) string... perhaps in the quotes? I'm just not
gt; gt; gt; gt; gt; familiar enough with the formula.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Heres how I am translating it in my mind... If Cell B1 on the sheet titled
gt; gt; gt; gt; gt; quot;Inputquot; equals... at this point I'm unsure what the quotes are to
gt; gt; gt; gt; gt; respresent... then... again make the cell value... again not sure what the
gt; gt; gt; gt; gt; quotes represent...if not, then input the information in Cell B1 of the
gt; gt; gt; gt; gt; quot;Inputquot; sheet.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I'm also a bit embarrassed to admit the I am a little confused about the
gt; gt; gt; gt; gt; lt;bggt; symbol you placed after the suggestion to name the input page Input lt;bggt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Like I said the direct input formula works fine for what I'm doing right
gt; gt; gt; gt; gt; now, I'm just afraid that I will run into a situation where your full formula
gt; gt; gt; gt; gt; will be necessary.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks so much for you assistance.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Mr. Ziggy
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; You could create an Input sheet. I'd name it Input lt;bggt;.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Then in column A, put a nice description. In column B, put your entry:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; A B
gt; gt; gt; gt; gt; gt; FirstName Ziggy
gt; gt; gt; gt; gt; gt; LastName Stardust
gt; gt; gt; gt; gt; gt; Salutation Mr (or Ms.)
gt; gt; gt; gt; gt; gt; ....
gt; gt; gt; gt; gt; gt; etc
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Then on your Forms sheet, you'd use a formula that points back to that input
gt; gt; gt; gt; gt; gt; sheet.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; =if(input!b1=quot;quot;,quot;quot;,input!b1)
gt; gt; gt; gt; gt; gt; (to grab the first name)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; You could even protect the forms sheet so that you can't overwrite the formulas:
gt; gt; gt; gt; gt; gt; tools|protection|protect sheet
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Mr. Ziggy wrote:
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; I have a specific Application Form that I must fill out by hand at each
gt; gt; gt; gt; gt; gt; gt; interview. I have created the form in Excel to allow me to type the info
gt; gt; gt; gt; gt; gt; gt; into the appropriate fields. This however labor intensive and it is easy to
gt; gt; gt; gt; gt; gt; gt; make a mistake that may change a different field.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Can I create separate listing or database (similar to the mail merge
gt; gt; gt; gt; gt; gt; gt; concept) that will just drop the info into the proper cells on the form with
gt; gt; gt; gt; gt; gt; gt; out me touching the form itself?
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

How do you take the data and populate a seperate sheet/form?

quot;Dave Petersonquot; wrote:

gt; If you only had 32 fields, you could use Data|Form for data entry.
gt;
gt; You may want to look at John Walkenbach's enhanced dataform:
gt; j-walk.com/ss/dataform/index.htm
gt;
gt;
gt;
gt; Laura Berry wrote:
gt; gt;
gt; gt; Dave:
gt; gt; My data is in Columns and I have at least 50 seperate quot;mergesquot; to do. Do I
gt; gt; have to change the reference for each form? i.e, the first form looks for
gt; gt; b1, the second form will need the data in b2, the third, b3, and so forth.
gt; gt; My data columns have First, Last etc.
gt; gt;
gt; gt; A B C
gt; gt; 1. First Last Address
gt; gt; 2. Mary Smith 123 lane dirv
gt; gt; 3. Larry Jones 456 Honeysuckle lane
gt; gt;
gt; gt; Thanks for your help
gt; gt; Laura
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; Someday, you may want to consider using a userform.
gt; gt; gt;
gt; gt; gt; Debra Dalgleish has some get started instructions for userforms at:
gt; gt; gt; contextures.com/xlUserForm01.html
gt; gt; gt;
gt; gt; gt; Mr. Ziggy wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; OMG!!!! lt;bggt; means quot;big grinquot;. Duh! LOL... sometimes I look at things too
gt; gt; gt; gt; deeply. I though there it be some techno. term I'm unfamiliar with. Perhaps
gt; gt; gt; gt; I need to send myself a (da) for dumbass. LOL
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks so much Dave for your help. That made things go so much smoother.
gt; gt; gt; gt; The next step is going to be finding a practical way to view the input page.
gt; gt; gt; gt; I'd love to be able to bring up individual data entry pages. Just type the
gt; gt; gt; gt; info for this section then hit the quot;nextquot; button and get the next section.
gt; gt; gt; gt; For now I just have the data fields set up in bordered cells all on one page.
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; =input!b1
gt; gt; gt; gt; gt; will return what's in B1 of the Input sheet.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; But if that cell is empty, you'll see a 0.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; So
gt; gt; gt; gt; gt; =if(input!b1=quot;quot;,quot;quot;,input!b1)
gt; gt; gt; gt; gt; says to check what's in B1. If it's empty (quot;quot;), then show nothing (quot;quot;). But if
gt; gt; gt; gt; gt; there's something there, show it instead.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I think you made a typo to get the #ref! error.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; And lt;bggt; just mean big grin. I'd name the input sheet quot;Inputquot; was kind of a
gt; gt; gt; gt; gt; feeble attempt at humor.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Mr. Ziggy wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Fantastic... works great. I did modify the command to just read =Input!B1
gt; gt; gt; gt; gt; gt; because I didn't quite understand how to work the formula you gave me.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; For example: =if(input!b1=quot;quot;,quot;quot;,input!b1). If I am only transfering
gt; gt; gt; gt; gt; gt; information, Why use an quot;ifquot; statement. When I put this in the form cell,
gt; gt; gt; gt; gt; gt; and put a sample information 1234 on cell b1 of the input page, I got a REF#
gt; gt; gt; gt; gt; gt; error.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I am assuming that I am supposed to have more information in the
gt; gt; gt; gt; gt; gt; =if(input!b1=quot;quot;,quot;quot;,input!b1) string... perhaps in the quotes? I'm just not
gt; gt; gt; gt; gt; gt; familiar enough with the formula.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Heres how I am translating it in my mind... If Cell B1 on the sheet titled
gt; gt; gt; gt; gt; gt; quot;Inputquot; equals... at this point I'm unsure what the quotes are to
gt; gt; gt; gt; gt; gt; respresent... then... again make the cell value... again not sure what the
gt; gt; gt; gt; gt; gt; quotes represent...if not, then input the information in Cell B1 of the
gt; gt; gt; gt; gt; gt; quot;Inputquot; sheet.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I'm also a bit embarrassed to admit the I am a little confused about the
gt; gt; gt; gt; gt; gt; lt;bggt; symbol you placed after the suggestion to name the input page Input lt;bggt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Like I said the direct input formula works fine for what I'm doing right
gt; gt; gt; gt; gt; gt; now, I'm just afraid that I will run into a situation where your full formula
gt; gt; gt; gt; gt; gt; will be necessary.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Thanks so much for you assistance.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Mr. Ziggy
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; You could create an Input sheet. I'd name it Input lt;bggt;.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Then in column A, put a nice description. In column B, put your entry:
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; A B
gt; gt; gt; gt; gt; gt; gt; FirstName Ziggy
gt; gt; gt; gt; gt; gt; gt; LastName Stardust
gt; gt; gt; gt; gt; gt; gt; Salutation Mr (or Ms.)
gt; gt; gt; gt; gt; gt; gt; ....
gt; gt; gt; gt; gt; gt; gt; etc
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Then on your Forms sheet, you'd use a formula that points back to that input
gt; gt; gt; gt; gt; gt; gt; sheet.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; =if(input!b1=quot;quot;,quot;quot;,input!b1)
gt; gt; gt; gt; gt; gt; gt; (to grab the first name)
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; You could even protect the forms sheet so that you can't overwrite the formulas:
gt; gt; gt; gt; gt; gt; gt; tools|protection|protect sheet
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Mr. Ziggy wrote:
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; I have a specific Application Form that I must fill out by hand at each
gt; gt; gt; gt; gt; gt; gt; gt; interview. I have created the form in Excel to allow me to type the info
gt; gt; gt; gt; gt; gt; gt; gt; into the appropriate fields. This however labor intensive and it is easy to
gt; gt; gt; gt; gt; gt; gt; gt; make a mistake that may change a different field.
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; Can I create separate listing or database (similar to the mail merge
gt; gt; gt; gt; gt; gt; gt; gt; concept) that will just drop the info into the proper cells on the form with
gt; gt; gt; gt; gt; gt; gt; gt; out me touching the form itself?
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;