How do I set up Data entry to match My Application Form

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 <bg>.

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="","",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:
>
> 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?

--

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="","",input!b1). If I am only transfering
information, Why use an "if" 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="","",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
"Input" 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
"Input" sheet.

I'm also a bit embarrassed to admit the I am a little confused about the
<bg> symbol you placed after the suggestion to name the input page Input <bg>

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

"Dave Peterson" wrote:

> You could create an Input sheet. I'd name it Input <bg>.
>
> 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="","",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:
> >
> > 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?
>
> --
>
> Dave Peterson
>

=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="","",input!b1)
says to check what's in B1. If it's empty (""), then show nothing (""). But if
there's something there, show it instead.

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

And <bg> just mean big grin. I'd name the input sheet "Input" was kind of a
feeble attempt at humor.





Mr. Ziggy wrote:
>
> 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="","",input!b1). If I am only transfering
> information, Why use an "if" 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="","",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
> "Input" 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
> "Input" sheet.
>
> I'm also a bit embarrassed to admit the I am a little confused about the
> <bg> symbol you placed after the suggestion to name the input page Input <bg>
>
> 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
>
> "Dave Peterson" wrote:
>
> > You could create an Input sheet. I'd name it Input <bg>.
> >
> > 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="","",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:
> > >
> > > 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?
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

OMG!!!! <bg> means "big grin". 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 "next" button and get the next section.
For now I just have the data fields set up in bordered cells all on one page.

"Dave Peterson" wrote:

> =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="","",input!b1)
> says to check what's in B1. If it's empty (""), then show nothing (""). But if
> there's something there, show it instead.
>
> I think you made a typo to get the #ref! error.
>
> And <bg> just mean big grin. I'd name the input sheet "Input" was kind of a
> feeble attempt at humor.
>
>
>
>
>
> Mr. Ziggy wrote:
> >
> > 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="","",input!b1). If I am only transfering
> > information, Why use an "if" 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="","",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
> > "Input" 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
> > "Input" sheet.
> >
> > I'm also a bit embarrassed to admit the I am a little confused about the
> > <bg> symbol you placed after the suggestion to name the input page Input <bg>
> >
> > 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
> >
> > "Dave Peterson" wrote:
> >
> > > You could create an Input sheet. I'd name it Input <bg>.
> > >
> > > 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="","",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:
> > > >
> > > > 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?
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Someday, you may want to consider using a userform.

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

Mr. Ziggy wrote:
>
> OMG!!!! <bg> means "big grin". 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 "next" button and get the next section.
> For now I just have the data fields set up in bordered cells all on one page.
>
> "Dave Peterson" wrote:
>
> > =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="","",input!b1)
> > says to check what's in B1. If it's empty (""), then show nothing (""). But if
> > there's something there, show it instead.
> >
> > I think you made a typo to get the #ref! error.
> >
> > And <bg> just mean big grin. I'd name the input sheet "Input" was kind of a
> > feeble attempt at humor.
> >
> >
> >
> >
> >
> > Mr. Ziggy wrote:
> > >
> > > 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="","",input!b1). If I am only transfering
> > > information, Why use an "if" 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="","",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
> > > "Input" 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
> > > "Input" sheet.
> > >
> > > I'm also a bit embarrassed to admit the I am a little confused about the
> > > <bg> symbol you placed after the suggestion to name the input page Input <bg>
> > >
> > > 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
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > You could create an Input sheet. I'd name it Input <bg>.
> > > >
> > > > 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="","",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:
> > > > >
> > > > > 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?
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

"Dave Peterson" wrote:

> Someday, you may want to consider using a userform.
>
> Debra Dalgleish has some get started instructions for userforms at:
> http://contextures.com/xlUserForm01.html
>
> Mr. Ziggy wrote:
> >
> > OMG!!!! <bg> means "big grin". 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 "next" button and get the next section.
> > For now I just have the data fields set up in bordered cells all on one page.
> >
> > "Dave Peterson" wrote:
> >
> > > =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="","",input!b1)
> > > says to check what's in B1. If it's empty (""), then show nothing (""). But if
> > > there's something there, show it instead.
> > >
> > > I think you made a typo to get the #ref! error.
> > >
> > > And <bg> just mean big grin. I'd name the input sheet "Input" was kind of a
> > > feeble attempt at humor.
> > >
> > >
> > >
> > >
> > >
> > > Mr. Ziggy wrote:
> > > >
> > > > 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="","",input!b1). If I am only transfering
> > > > information, Why use an "if" 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="","",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
> > > > "Input" 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
> > > > "Input" sheet.
> > > >
> > > > I'm also a bit embarrassed to admit the I am a little confused about the
> > > > <bg> symbol you placed after the suggestion to name the input page Input <bg>
> > > >
> > > > 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
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > You could create an Input sheet. I'd name it Input <bg>.
> > > > >
> > > > > 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="","",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:
> > > > > >
> > > > > > 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?
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Dave:
My data is in Columns and I have at least 50 seperate "merges" 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

"Dave Peterson" wrote:

> Someday, you may want to consider using a userform.
>
> Debra Dalgleish has some get started instructions for userforms at:
> http://contextures.com/xlUserForm01.html
>
> Mr. Ziggy wrote:
> >
> > OMG!!!! <bg> means "big grin". 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 "next" button and get the next section.
> > For now I just have the data fields set up in bordered cells all on one page.
> >
> > "Dave Peterson" wrote:
> >
> > > =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="","",input!b1)
> > > says to check what's in B1. If it's empty (""), then show nothing (""). But if
> > > there's something there, show it instead.
> > >
> > > I think you made a typo to get the #ref! error.
> > >
> > > And <bg> just mean big grin. I'd name the input sheet "Input" was kind of a
> > > feeble attempt at humor.
> > >
> > >
> > >
> > >
> > >
> > > Mr. Ziggy wrote:
> > > >
> > > > 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="","",input!b1). If I am only transfering
> > > > information, Why use an "if" 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="","",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
> > > > "Input" 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
> > > > "Input" sheet.
> > > >
> > > > I'm also a bit embarrassed to admit the I am a little confused about the
> > > > <bg> symbol you placed after the suggestion to name the input page Input <bg>
> > > >
> > > > 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
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > You could create an Input sheet. I'd name it Input <bg>.
> > > > >
> > > > > 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="","",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:
> > > > > >
> > > > > > 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?
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

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:
http://j-walk.com/ss/dataform/index.htm



Laura Berry wrote:
>
> Dave:
> My data is in Columns and I have at least 50 seperate "merges" 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
>
> "Dave Peterson" wrote:
>
> > Someday, you may want to consider using a userform.
> >
> > Debra Dalgleish has some get started instructions for userforms at:
> > http://contextures.com/xlUserForm01.html
> >
> > Mr. Ziggy wrote:
> > >
> > > OMG!!!! <bg> means "big grin". 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 "next" button and get the next section.
> > > For now I just have the data fields set up in bordered cells all on one page.
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > =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="","",input!b1)
> > > > says to check what's in B1. If it's empty (""), then show nothing (""). But if
> > > > there's something there, show it instead.
> > > >
> > > > I think you made a typo to get the #ref! error.
> > > >
> > > > And <bg> just mean big grin. I'd name the input sheet "Input" was kind of a
> > > > feeble attempt at humor.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > Mr. Ziggy wrote:
> > > > >
> > > > > 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="","",input!b1). If I am only transfering
> > > > > information, Why use an "if" 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="","",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
> > > > > "Input" 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
> > > > > "Input" sheet.
> > > > >
> > > > > I'm also a bit embarrassed to admit the I am a little confused about the
> > > > > <bg> symbol you placed after the suggestion to name the input page Input <bg>
> > > > >
> > > > > 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
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > You could create an Input sheet. I'd name it Input <bg>.
> > > > > >
> > > > > > 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="","",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:
> > > > > > >
> > > > > > > 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?
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

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

"Dave Peterson" wrote:

> 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:
> http://j-walk.com/ss/dataform/index.htm
>
>
>
> Laura Berry wrote:
> >
> > Dave:
> > My data is in Columns and I have at least 50 seperate "merges" 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
> >
> > "Dave Peterson" wrote:
> >
> > > Someday, you may want to consider using a userform.
> > >
> > > Debra Dalgleish has some get started instructions for userforms at:
> > > http://contextures.com/xlUserForm01.html
> > >
> > > Mr. Ziggy wrote:
> > > >
> > > > OMG!!!! <bg> means "big grin". 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 "next" button and get the next section.
> > > > For now I just have the data fields set up in bordered cells all on one page.
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > =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="","",input!b1)
> > > > > says to check what's in B1. If it's empty (""), then show nothing (""). But if
> > > > > there's something there, show it instead.
> > > > >
> > > > > I think you made a typo to get the #ref! error.
> > > > >
> > > > > And <bg> just mean big grin. I'd name the input sheet "Input" was kind of a
> > > > > feeble attempt at humor.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > Mr. Ziggy wrote:
> > > > > >
> > > > > > 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="","",input!b1). If I am only transfering
> > > > > > information, Why use an "if" 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="","",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
> > > > > > "Input" 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
> > > > > > "Input" sheet.
> > > > > >
> > > > > > I'm also a bit embarrassed to admit the I am a little confused about the
> > > > > > <bg> symbol you placed after the suggestion to name the input page Input <bg>
> > > > > >
> > > > > > 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
> > > > > >
> > > > > > "Dave Peterson" wrote:
> > > > > >
> > > > > > > You could create an Input sheet. I'd name it Input <bg>.
> > > > > > >
> > > > > > > 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="","",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:
> > > > > > > >
> > > > > > > > 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?
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

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

"Dave Peterson" wrote:

> 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:
> http://j-walk.com/ss/dataform/index.htm
>
>
>
> Laura Berry wrote:
> >
> > Dave:
> > My data is in Columns and I have at least 50 seperate "merges" 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
> >
> > "Dave Peterson" wrote:
> >
> > > Someday, you may want to consider using a userform.
> > >
> > > Debra Dalgleish has some get started instructions for userforms at:
> > > http://contextures.com/xlUserForm01.html
> > >
> > > Mr. Ziggy wrote:
> > > >
> > > > OMG!!!! <bg> means "big grin". 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 "next" button and get the next section.
> > > > For now I just have the data fields set up in bordered cells all on one page.
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > =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="","",input!b1)
> > > > > says to check what's in B1. If it's empty (""), then show nothing (""). But if
> > > > > there's something there, show it instead.
> > > > >
> > > > > I think you made a typo to get the #ref! error.
> > > > >
> > > > > And <bg> just mean big grin. I'd name the input sheet "Input" was kind of a
> > > > > feeble attempt at humor.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > Mr. Ziggy wrote:
> > > > > >
> > > > > > 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="","",input!b1). If I am only transfering
> > > > > > information, Why use an "if" 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="","",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
> > > > > > "Input" 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
> > > > > > "Input" sheet.
> > > > > >
> > > > > > I'm also a bit embarrassed to admit the I am a little confused about the
> > > > > > <bg> symbol you placed after the suggestion to name the input page Input <bg>
> > > > > >
> > > > > > 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
> > > > > >
> > > > > > "Dave Peterson" wrote:
> > > > > >
> > > > > > > You could create an Input sheet. I'd name it Input <bg>.
> > > > > > >
> > > > > > > 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="","",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:
> > > > > > > >
> > > > > > > > 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?
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Uhhh. Huh?

Personally, I'd do my best to keep all the data in one worksheet. Then if I
needed to split it up into separate sheets, I start from that consolidated
sheet.

Without knowing what you're really asking....

You may want to try Ron de Bruin's Easyfilter:
http://www.rondebruin.nl/easyfilter.htm

Or look at the way Debra Dalgleish does it:

Debra's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

or

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb



Danielle44 wrote:
>
> How do you take the data and populate a seperate sheet/form?
>
> "Dave Peterson" wrote:
>
> > 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:
> > http://j-walk.com/ss/dataform/index.htm
> >
> >
> >
> > Laura Berry wrote:
> > >
> > > Dave:
> > > My data is in Columns and I have at least 50 seperate "merges" 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
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Someday, you may want to consider using a userform.
> > > >
> > > > Debra Dalgleish has some get started instructions for userforms at:
> > > > http://contextures.com/xlUserForm01.html
> > > >
> > > > Mr. Ziggy wrote:
> > > > >
> > > > > OMG!!!! <bg> means "big grin". 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 "next" button and get the next section.
> > > > > For now I just have the data fields set up in bordered cells all on one page.
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > =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="","",input!b1)
> > > > > > says to check what's in B1. If it's empty (""), then show nothing (""). But if
> > > > > > there's something there, show it instead.
> > > > > >
> > > > > > I think you made a typo to get the #ref! error.
> > > > > >
> > > > > > And <bg> just mean big grin. I'd name the input sheet "Input" was kind of a
> > > > > > feeble attempt at humor.
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > Mr. Ziggy wrote:
> > > > > > >
> > > > > > > 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="","",input!b1). If I am only transfering
> > > > > > > information, Why use an "if" 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="","",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
> > > > > > > "Input" 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
> > > > > > > "Input" sheet.
> > > > > > >
> > > > > > > I'm also a bit embarrassed to admit the I am a little confused about the
> > > > > > > <bg> symbol you placed after the suggestion to name the input page Input <bg>
> > > > > > >
> > > > > > > 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
> > > > > > >
> > > > > > > "Dave Peterson" wrote:
> > > > > > >
> > > > > > > > You could create an Input sheet. I'd name it Input <bg>.
> > > > > > > >
> > > > > > > > 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="","",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:
> > > > > > > > >
> > > > > > > > > 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?
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > Dave Peterson
> > > > > > > >
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson