Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Grid to Template/Form?
Hey guys-
Just wondering here- I have a standard Excel sheet with all my cols and rows and formulas. I then typically mail merge that into a Word Form/Template for my final printout (this has all the corresponding fields in the form). I am wondering if I can just keep all this in Excel- within 1 file. So, what that looks like is I have my standard Excel grid as normal, but then, maybe on another worksheet, my Template/Form thing that I created that merges the fields from the grid into the print-pretty version of the form. I can then scroll through my forms as if I am scrolling down the rows on the grid. Does that make sense? I just want to avoid mail merging into Word each time I want to print out a record, and would like to be able to scroll through the records right within Excel, but on the Print-pretty form that I created. So I can switch between the standard Excel grid, and, the Form. Can this be done, and if so, HOW??? I can figure out how to create the form with the fields, I'm sure- I just don't know how to have it all within one Excel file and have it all linked in real time so that if I change something in the grid, it'll automatically update the form. BTW- this will be used mainly for VIEWING the data- but has the ability to make the changes right off the form as well. Also- there are well over 32 fields in my database- all of which need to appear on this form/mail merge template. Is this possible? Thanks for any help on this one! D |
#2
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Grid to Template/Form?
use linking formulas based on indirect (this formula would be in your form).
=Indirect("Sheet1!A" & Sheet1!$M9) where M9 holds the row (record) you want to view in your form. Change the value in M9 and you will see a new record. You can make a spin button to select which record (have it change the value in M9). This is a one way link, however. You can't update in the Form. If you want to do that, you would have skip using formulas and do extensive programming in the Worksheet_Change event. -- Regards, Tom Ogilvy "D" wrote in message news:vLlPc.22368$8G6.2315@fed1read04... Hey guys- Just wondering here- I have a standard Excel sheet with all my cols and rows and formulas. I then typically mail merge that into a Word Form/Template for my final printout (this has all the corresponding fields in the form). I am wondering if I can just keep all this in Excel- within 1 file. So, what that looks like is I have my standard Excel grid as normal, but then, maybe on another worksheet, my Template/Form thing that I created that merges the fields from the grid into the print-pretty version of the form. I can then scroll through my forms as if I am scrolling down the rows on the grid. Does that make sense? I just want to avoid mail merging into Word each time I want to print out a record, and would like to be able to scroll through the records right within Excel, but on the Print-pretty form that I created. So I can switch between the standard Excel grid, and, the Form. Can this be done, and if so, HOW??? I can figure out how to create the form with the fields, I'm sure- I just don't know how to have it all within one Excel file and have it all linked in real time so that if I change something in the grid, it'll automatically update the form. BTW- this will be used mainly for VIEWING the data- but has the ability to make the changes right off the form as well. Also- there are well over 32 fields in my database- all of which need to appear on this form/mail merge template. Is this possible? Thanks for any help on this one! D |
#3
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Grid to Template/Form?
Tom- thanks for this reply, and many of the other replies you've given in
the past. Your assistance has been crucial! This sounds as if it'll work. So basically, I create a worksheet, and through merging cells, and manipulating the format of it, I make my form/template. Then, in the cells I want the data to show through, I use this 'Indirect' method as you stated earlier. In order to scroll through the sheets, I need to create this button. Makes sense, but obviously if I have say, 100 pieces of info for each record, I'd have to change the M9 reference on each cell, correct? Is there an easier way to do this? Maybe setup a variable of some sort that all the cells link to that ONE reference variable, and the button changes just that variable by +1 or -1 for example? ALSO- in the long run, I'd like to be able to update through this form. For example- I'd like to be able to select certain records from the table, and email out a file with just those records in it (as well as a userform I created). The employee will get the email, and update certain fields using the form and email it back to me. When I receive the file back, I can merge in the data they changed and update my master database. Is this possible to do in Excel? Thanks again for all your help! D "Tom Ogilvy" wrote in message ... use linking formulas based on indirect (this formula would be in your form). =Indirect("Sheet1!A" & Sheet1!$M9) where M9 holds the row (record) you want to view in your form. Change the value in M9 and you will see a new record. You can make a spin button to select which record (have it change the value in M9). This is a one way link, however. You can't update in the Form. If you want to do that, you would have skip using formulas and do extensive programming in the Worksheet_Change event. -- Regards, Tom Ogilvy "D" wrote in message news:vLlPc.22368$8G6.2315@fed1read04... Hey guys- Just wondering here- I have a standard Excel sheet with all my cols and rows and formulas. I then typically mail merge that into a Word Form/Template for my final printout (this has all the corresponding fields in the form). I am wondering if I can just keep all this in Excel- within 1 file. So, what that looks like is I have my standard Excel grid as normal, but then, maybe on another worksheet, my Template/Form thing that I created that merges the fields from the grid into the print-pretty version of the form. I can then scroll through my forms as if I am scrolling down the rows on the grid. Does that make sense? I just want to avoid mail merging into Word each time I want to print out a record, and would like to be able to scroll through the records right within Excel, but on the Print-pretty form that I created. So I can switch between the standard Excel grid, and, the Form. Can this be done, and if so, HOW??? I can figure out how to create the form with the fields, I'm sure- I just don't know how to have it all within one Excel file and have it all linked in real time so that if I change something in the grid, it'll automatically update the form. BTW- this will be used mainly for VIEWING the data- but has the ability to make the changes right off the form as well. Also- there are well over 32 fields in my database- all of which need to appear on this form/mail merge template. Is this possible? Thanks for any help on this one! D |
#4
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Grid to Template/Form?
All indirect formulas would refer to Cell M9 to pick up the row number.
What would vary for each formula is which column to get their data from. If the column number can be calculated relative to the position of the formula you can put such calculations in the indirect function =indirect("Sheet1!" & Address(Sheet1!$M$9,Column()+3)) as an example. You could also use Offset or Index as a means to refer to a cell using a calculation. What would be the best approach (least work) would require specific knowledge of you data and your layout. If you want people to update the data, yes, you can extract subsets of the data using Autofilter as an example, put those in a Template that contains a userform and have people update the data. You have to write all the code though. http://j-walk.com/ss/excel/tips/tip84.htm http://support.microsoft.com/default...b;en-us;829070 How to use Visual Basic for Applications examples to control UserForms in Microsoft Excel XL97: How to Use a UserForm for Entering Data (Q161514) http://support.microsoft.com/?id=161514 XL2000: How to Use a UserForm for Entering Data (Q213749) http://support.microsoft.com/?id=213749 http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm Lesson 11: Creating a Custom Form Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step. http://support.microsoft.com/?id=168067 File Title: Microsoft(R) Visual Basic(R) for Applications Examples for Controlling UserForms in Microsoft Excel 97 File Name: WE1163.EXE File Size: 161742 bytes File Date: 05/08/97 Keywords: kbfile Description: This Application Note is an introduction to manipulating UserForms in Microsoft Excel 97. It includes examples and Microsoft Visual Basic for Applications macros that show you how to take advantage of the capabilities of UserForms and use each of the ActiveX controls that are available for UserForms Peter Aiken Articles: Part I http://msdn.microsoft.com/library/en...FormsPartI.asp Part II http://msdn.microsoft.com/library/en...ormsPartII.asp -- Regards, Tom Ogilvy "D" wrote in message news:q0uPc.35322$8G6.14472@fed1read04... Tom- thanks for this reply, and many of the other replies you've given in the past. Your assistance has been crucial! This sounds as if it'll work. So basically, I create a worksheet, and through merging cells, and manipulating the format of it, I make my form/template. Then, in the cells I want the data to show through, I use this 'Indirect' method as you stated earlier. In order to scroll through the sheets, I need to create this button. Makes sense, but obviously if I have say, 100 pieces of info for each record, I'd have to change the M9 reference on each cell, correct? Is there an easier way to do this? Maybe setup a variable of some sort that all the cells link to that ONE reference variable, and the button changes just that variable by +1 or -1 for example? ALSO- in the long run, I'd like to be able to update through this form. For example- I'd like to be able to select certain records from the table, and email out a file with just those records in it (as well as a userform I created). The employee will get the email, and update certain fields using the form and email it back to me. When I receive the file back, I can merge in the data they changed and update my master database. Is this possible to do in Excel? Thanks again for all your help! D "Tom Ogilvy" wrote in message ... use linking formulas based on indirect (this formula would be in your form). =Indirect("Sheet1!A" & Sheet1!$M9) where M9 holds the row (record) you want to view in your form. Change the value in M9 and you will see a new record. You can make a spin button to select which record (have it change the value in M9). This is a one way link, however. You can't update in the Form. If you want to do that, you would have skip using formulas and do extensive programming in the Worksheet_Change event. -- Regards, Tom Ogilvy "D" wrote in message news:vLlPc.22368$8G6.2315@fed1read04... Hey guys- Just wondering here- I have a standard Excel sheet with all my cols and rows and formulas. I then typically mail merge that into a Word Form/Template for my final printout (this has all the corresponding fields in the form). I am wondering if I can just keep all this in Excel- within 1 file. So, what that looks like is I have my standard Excel grid as normal, but then, maybe on another worksheet, my Template/Form thing that I created that merges the fields from the grid into the print-pretty version of the form. I can then scroll through my forms as if I am scrolling down the rows on the grid. Does that make sense? I just want to avoid mail merging into Word each time I want to print out a record, and would like to be able to scroll through the records right within Excel, but on the Print-pretty form that I created. So I can switch between the standard Excel grid, and, the Form. Can this be done, and if so, HOW??? I can figure out how to create the form with the fields, I'm sure- I just don't know how to have it all within one Excel file and have it all linked in real time so that if I change something in the grid, it'll automatically update the form. BTW- this will be used mainly for VIEWING the data- but has the ability to make the changes right off the form as well. Also- there are well over 32 fields in my database- all of which need to appear on this form/mail merge template. Is this possible? Thanks for any help on this one! D |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a template form, advance a form number everytime you open | Excel Discussion (Misc queries) | |||
Grid lines in Excel not showing.Have tools,options,view/grid cked | Excel Discussion (Misc queries) | |||
How can I remove a Toggle Grid from a pre existing template? | Charts and Charting in Excel | |||
how to generate a unique form # when using an excel form template | Excel Worksheet Functions | |||
need form template for excel | Excel Discussion (Misc queries) |