Mail Merge or Complete Excel Method

[spectraflame]

I have a spreadsheet containing multiple rows. The end goal is to take key values from various cells and populate a form. I am familiar with using Excel and Word to complete a mail merge which I think would work, however I am curious to know if there is an all Excel method to accomplish the same thing. My concern is with the Excel to Word mail merge solution, I will be forced to have a single record on a single page however the data in Excel contains rows that are related based on customer number. Some records are unique and some records vary in related rows. I would appreciate input from the group on what would be the best way to accomplish this. The end product will be used by someone with very limited knowledge of Excel or the mail merge process.

 

[bbotzong]

I've done this quite a few times... I use a new Sheet and create my form. At the top, I put a reference to the row where the data is stored. The non-change cells are entered as text (i.e., Name, Address). In the cell where the data goes, just create a formula that points back to the data tab. Change the 'row' number and the data will change. Here's an example:

Book1
	A	B	C	D
1		Row	3
2
3	Name	David
4	Address	123 East Side
5	City	Frankston
6
7
Form

Cell Formulas
Range	Formula
B3:B5	B3	=INDIRECT(ADDRESS($C$1,MATCH(A3,Data!$A$1:$C$1,0),,,"Data"))

and

Book1
	A	B	C
1	Name	Address	City
2	Alice	221 Baker Street	Carlton
3	David	123 East Side	Frankston
4	Guillermo	222 Harvard Square	Iowa City
5
Data

 

[spectraflame]

So are you saying you just manually change the C1 reference to populate the form and then Print each one?

 

[bbotzong]

That's one way to do it. You can write a quick macro to change the numbers automatically when you click on a link or button.