linking data to a form

[Nancy]

I would like to be able to have a link that pulls certain data from each row
into an excel form.
Each of our orders are entered into Excel on seperate rows. Is it possible
to have a link "button" that opens an Excel form with data from a few of the
columns? I do not need to save the forms, just pull up, print and close. I
thought about mail merge, but was hoping for an even more simple solution.

[Richard Buttrey]

On Thu, 20 Apr 2006 00:21:01 -0700, Nancy
<[email protected]> wrote:

>I would like to be able to have a link that pulls certain data from each row
>into an excel form.
>Each of our orders are entered into Excel on seperate rows. Is it possible
>to have a link "button" that opens an Excel form with data from a few of the
>columns? I do not need to save the forms, just pull up, print and close. I
>thought about mail merge, but was hoping for an even more simple solution.


Have you checked out Data-->Form from the main menu?

Do this with the active cell anywhere in your table of data.

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[Nancy]

I thought the data form was just for entering the data.

Currently my sales dept is entering data on one form (individual form for
each order), then the AR dept then re-enters that information in their data
file and adds to it.

What I want to do is:
Have the sales department enter the data into the data file, and then be
able to click a hyperlink that will bring up the form with the correct info
for each order. Eliminates the double entry. They do not need to save the
form, just print it.

I have tried IF, LOOKUP, SUMPRODUCT....but can't find the right formula to
make this work.

I have 2 options:
Preference is to be able to click the link and the entire form is fill with
the data to print
Other option is to hit the link to bring up the form, then have them fill in
a trigger # (serial #) and then it will fill the form.

I did see some info on the worksheet form, but am not sure how to create that.

Thank you



"Richard Buttrey" wrote:

> On Thu, 20 Apr 2006 00:21:01 -0700, Nancy
> <[email protected]> wrote:
>
> >I would like to be able to have a link that pulls certain data from each row
> >into an excel form.
> >Each of our orders are entered into Excel on seperate rows. Is it possible
> >to have a link "button" that opens an Excel form with data from a few of the
> >columns? I do not need to save the forms, just pull up, print and close. I
> >thought about mail merge, but was hoping for an even more simple solution.
>
>
> Have you checked out Data-->Form from the main menu?
>
> Do this with the active cell anywhere in your table of data.
>
> HTH
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________
>

[Richard Buttrey]

On Thu, 20 Apr 2006 10:12:01 -0700, Nancy
<[email protected]> wrote:

>I thought the data form was just for entering the data.

Usually yes, but sometimes useful for handling data already in a
table.

>Currently my sales dept is entering data on one form (individual form for
>each order), then the AR dept then re-enters that information in their data
>file and adds to it.
>
>What I want to do is:
>Have the sales department enter the data into the data file, and then be
>able to click a hyperlink that will bring up the form with the correct info
>for each order. Eliminates the double entry. They do not need to save the
>form, just print it.
>
>I have tried IF, LOOKUP, SUMPRODUCT....but can't find the right formula to
>make this work.
>
>I have 2 options:
>Preference is to be able to click the link and the entire form is fill with
>the data to print
>Other option is to hit the link to bring up the form, then have them fill in
>a trigger # (serial #) and then it will fill the form.
>
>I did see some info on the worksheet form, but am not sure how to create that.
>
>Thank you

Is the data file set up as a table of rows, one for each order, with
the columns being fields of data?

When you say you want to 'click the link', what exactly do you mean?
Are you saying that you want to 'click' or select one of the cells in
the order row you're interested and have the form completed. If so
that would be best achieved with a simple VBA macro re-acting to the
Worksheet BeforeDoubleClick Event, and populating a cell with your
trigger value, which then become the subject value in VLookup formulae
embedded in the appropriate places in your form.

Without a VBA macro it would mean manually entering a trigger value,
serial, order number,

If the data is in a typical layout

A B C D E F
O/No Customer Product Qty Price Total
1
2
3

Name the whole database say "Data"

Then on your form name the cell which will contain your trigger value,
(Order / Serial No. etc) "Trigger"

Now put the following in the worksheet BeforeDoubleClick event.
Note this assumes the trigger field is in column A - that's the
reference to Cells(1,1) below. If it's different, say column C (or 3)
then just change the second part to Cells(1,3)


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)

If Not Intersect(Target, ActiveCell) = "" Then

Range("Trigger") = ActiveCell.EntireRow.Cells(1, 1)

End If

End Sub


Whenever a row in the database is double clicked, the cell you've
named "Trigger" is populated with the appropriate value.

Now it's just a question of putting VLOOKUP formulae in the
appropriate places in your form layout.

e.g. =VLOOKUP(Trigger,Data,5,false) change the number (5) to reflect
the position in the database for each field you have on the form.

This assumes the Trigger field is in column A of the database. If it's
not you'll need to modify it with an Offset

=VLOOKUP(Trigger,OFFSET(Data,0,1),2,FALSE)

changing the offsets as appropriate

HTH. Please post back if I've misunderstood your requirement.

Rgds


>
>
>
>"Richard Buttrey" wrote:
>
>> On Thu, 20 Apr 2006 00:21:01 -0700, Nancy
>> <[email protected]> wrote:
>>
>> >I would like to be able to have a link that pulls certain data from each row
>> >into an excel form.
>> >Each of our orders are entered into Excel on seperate rows. Is it possible
>> >to have a link "button" that opens an Excel form with data from a few of the
>> >columns? I do not need to save the forms, just pull up, print and close. I
>> >thought about mail merge, but was hoping for an even more simple solution.
>>
>>
>> Have you checked out Data-->Form from the main menu?
>>
>> Do this with the active cell anywhere in your table of data.
>>
>> HTH
>> __
>> Richard Buttrey
>> Grappenhall, Cheshire, UK
>> __________________________
>>

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________