Populating a standard form from an Excel worksheet.

[weathergeek]

I have a single standard form that I want to add data from a worksheet. The worksheet has 278 individual rows of data. The worksheet has 21 columns of data that corresponds to 21 cells on the standard form. Some of these columns are text and some numeric. This form is a survey that I want to add previous data to. What I want to do is populate this form so it generates 278 individual forms with the corresponding data. I want it to work like Mail Merge does in MS Word. I've never used code (?) that I have been reading about on this forum. I know I can follow explicit instructions. Any help out there? Thanks.

 

[Norie]

Have you considered using VLOOKUP worksheet functions?

This could work if each row has a unique identfier in the first column.

Can you explain further what you actually mean by a standard form?

Do you mean a worksheet or a userform?

 

[OdinsDream]

So you have a list of data in column format that you'd like to use, row-by-row, to fill in a template, then print this template? Coding this would be fairly straightforward, but you'll need to further clarify your situation and the desired results.

 

[weathergeek]

I've developed a form in Excel. It is basically a list of survey questions. We conduct this survey annually so the previous years data is provided for comparison. The previous years data is located in a Excel spreadsheet that has 278 rows for the 278 survey respondants. There are seventeen questions to the survey. Each row is unique in that the first cell includes the location of the respondant. I use this cell as a title to the survey. Instead of copying and pasting or using individual formulas, there must be a way to transfer the data from the spreadsheet to the form/survey I have created so that I can create 278 surveys that I can provide my respondants. I know how to link cells from two worksheets one at a time but that would take forever to do 278 this way. Hopefully this clears things up a little. Thanks.

 

[OdinsDream]

In each of your cells that you require some kind of answer from one of the 278 rows, you could do something like this:

Put a cell on your template that you can type a row number in. This will be which row the sheet should pull the information from. In my example, I put this number in B1.

Now, in any of the other cells where data is supposed to show up, put something like this:

=INDIRECT("Sheet2!A"&B1+1)

If cell B1 (where your row number goes) contained the number "2", then this formula would show you data from Sheet2, cell A3.

I'm adding +1 to the rownumber in order to account for a header row in your data page. If you didn't have a header row, just take out the plus one.

You'd repeat this formula in each of the cells that need to be filled in, changing the "A" in this case to whatever column that data should come from. Is this what you need?