Multi-source Mail Merge

users
#1

I have multiple spreadsheet files that each represent one unique item. As an analogy, think of the spreadsheet as a basket that contains a variety of eggs (both calculated and hand-entered data). I have multiple baskets each with multiple eggs. Each basket (spreadsheet) is associated with one person. Each person can have multiple baskets (although that's probably not relevant).

I also have a letter, with which I want to do a mail merge - against each spreadsheet. i.e. each letter needs to draw multiple pieces of data (eggs) from each spreadsheet, one letter per spreadsheet.

Normally to do a mail merge you have one data source. The problem of course is that I don't have one data source, I have an unknown number - each spreadsheet is a data source. So I'm at a loss as to how to mail merge against multiple data sources in an automated fashion.

Thank you in advance.

#2

This sounds like an interesting problem.

So, just doing it manually, how do *you* know how many spreadsheets are
required for any particular result?

Mark Stanton
One small step for mankind...

#3

If one person has multiple baskets (=sheets), will this person receive one letter? or multiple letters?

This looks like (in SQL):
SELECT egg1, egg2, egg3 FROM basket1
UNION
SELECT egg2, egg3, egg4 FROM basket2
UNION
SELECT egg3, egg4, egg5 FROM basket3

How equal/similar are these eggs, and the number of eggs in a basket?

#4

Merge all sheets into one and do what you want with it:

#5

Thank you for your response. I looked at the link you provided. That's an interesting solution, but I'm not sure it would work. It's probably important to know that there are multiple sheets in each spreadsheet file. Also, I don't know how I'd find the cells I need once the files are merged. For example if I need data from A2, C3, `sheet2`D11, F32 and G12 in each file, merging the files would mean A2 is suddenly A42 or some such for what was file2. I don't see how to make this work.

#6

Thank you for your response. Each letter to be generated relies on one single spreadsheet for all its data.

#7

Would it be possible to create a 'master' spreadsheet, linked to the
various single spreadsheets and then do the mail merge from the
'master'?

#8

This is always the trouble when you clutter your data across many files.
This may be solvable when your values can be looked up from the merged
table.
Possibly you need a completely different way to organize your data.

#9

That's a really good idea in concept, however in practice I don't know how it would work. I presume you're saying that the master spreadsheet would contain filenames of all the other spreadsheets? That's fine except I don't see how that helps, as each file needs to be opened by the mail merge function one at a time and there doesn't seem to be a way to automate that.

Unless you're saying that the master would contain just the needed cells from the other spreadsheets? That seems interesting... it would solve the mail merge problem as then I'd only have one data source, but I'd have to hand enter each file name, modifying each cell one at a time. That doesn't sound too appealing.

#10

You copy from the cells in the various spreadsheets and do a Paste
Special to the 'master', selecting 'Link'. That way, any changes made
to the various spreadsheets is reflected in the 'master'. (Ranges can
be copied and linked with a single Paste Special — no need to copy and
re-enter one cell at a time.)

#11

I see what you're saying now. I may end up trying that if no better alternatives present themselves.

Thank you