Using multple spreadsheets to create a mailmerge

[Yogi]

Hi,

I need to create a mail merge where data is spread out in multiple
spreadhsheets. I tried copying all required fields in one spreadsheet that I
could use but it does not work as each spreadsheet uses several formulas
which for some reason dont want to transfer to the new spreadsheet. Is there
a way I can create a merge using multiple spreadhsheets?

 

[Peter Jamieson]

A general approach to this is described in In theory, you can do this using
an approach along the lines of the one described in

http://support.microsoft.com/kb/211802/en-us

However, it is very difficult to get this right, even when the data is in
separate sheets in the same workbook. Since you are already having
difficulty simply copying worksheet data into a single worksheet, I think it
is even less likely that this apporach will work for you.

Which means that you have little alternative but to try to copy the data
into a sigle place, then use that as the data source for the merge, or do
several merges, each based on a different data source.

If you have fewer than around 63/54 columns and are only doing this merge
once, you could try
a. copying each data source into a new table in a Word document
b. modifying each table so that it has the same set of columns
c. joining the tables together
d. using that as the data source

Otherwise, it /may/ help to
a. export each sheet to .csv format (which should remove any Excel
formulas)
b. re-import to a new Excel workbook
c. modify each sheet so that it has the same set of columns
d. copy the sheets into a single sheet
e. use that as the data source