Excel Grid to Template/Form?

[D]

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

 

[Tom Ogilvy]

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.

 

[D]

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]

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.aspx?scid=kb;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-us/dnoffpro01/html/IntroductiontoUserFormsPartI.asp
Part II
http://msdn.microsoft.com/library/en-us/dnoffsol02/html/IntroductiontoUserFormsPartII.asp

--
Regards,
Tom Ogilvy