Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
linking data to a form
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
linking data to a form
On Thu, 20 Apr 2006 00:21:01 -0700, Nancy
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 __________________________ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
linking data to a form
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 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 __________________________ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
linking data to a form
On Thu, 20 Apr 2006 10:12:01 -0700, Nancy
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 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 __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set up Data entry to match My Application Form | Excel Discussion (Misc queries) | |||
Excel Form and Data Retrieval | Excel Worksheet Functions | |||
Saving data to a form datasheet | Excel Discussion (Misc queries) | |||
ranking query | Excel Discussion (Misc queries) | |||
format cell from data input to output form | Excel Worksheet Functions |