Complicated Excel Mailing List

[michaeld.rooney]

This is a doozie of a problem, and I was hoping someone with a
little more experience could help me out.
My boss has commissioned me to create a cvs file from excel to be
used as a mailing list for our company. It will include something like
rows of first and last names, phone numbers, address, etc with
information gathered from several sources.
How do I create a template where I can import another workbook and
have say, column H from that transfered to column G in the template
automatically, etc, and also have it place the 6th and 7th digits of
one column into another (i.e. D5 from the import is '4398710983' which
puts '10' into D5 in the template)? Any assistance on this would be
greatly appreciated.. -Mike

 

[Guest]

Do you know that the layout of the data in those other workbooks is always
going to be the same? If they are, then it's not too difficult:

You set up the formulas needed on your sheet that will become the .csv file
(I'll call it MailingListSheet for discussion here) to 'map' from the
imported sheets into the MailingListSheet and to do any manipulation of data
from the imported sheets during the mapping. This will take a little manual
setup on your part initially. But after that it becomes a matter of
importing a new sheet from another workbook and then copying your formulas
down for enough rows to accomodate the new data and then using Edit | Replace
to change the sheet name in the formulas for the new sheet to refer to that
sheet.

But if each potential source of information to be added to the
MailingListSheet has a different layout, then you'll have to deal with each
individually as you import the foreign sheets into your workbook.

Since the .csv file is only going to be a text representation of the
contents of the cells in the MailingListSheet, you could just link to the
values in the needed sheets in the other books. What I said earlier could
still apply - you'd just need to Edit | Replace both the workbook and
worksheet names in each group. This would offer the advantage of always
updating from those other workbooks if their contents change, and you just
have to export the .cvs file to keep it updated in the future.

Are you going to need help with things like special formulas to do the
extraction of partial data like the selection of the 6th and 7th digit
operation you mentioned? If so, post as separate issues when specifics of
them are known. But a quick one for doing that would be a formula like this:
=MID('some other sheet'!D5,6,2)
says start at character #6 in D5 on 'some other sheet' and return 2
characters beginning at character #6.