linking between spreadsheets

[Ed]

Hi,

I am trying to to link data from 4 various spreadsheets, I'll call them sub
spreadsheets, to one master spreadsheet. The four various spreadsheets are
each similar in structure, but each is a log of a different case type. For
example sub spreadsheet #1 is a running list of cases related to zone
variances. The zone variances are assigned to various people. Similarly, sub
spreadsheet #2 is a running list of cases related to conditional use permits
also assigned to various people. So, at any one time a single person may have
a variety of case types (i.e. zone variance, conditional use, etc.) assigned
to them. There are also sub spreadsheets for two other types of cases, and
maybe more in the future. From these four sub spreadsheets I want to create a
master spreadsheet with approximately 15 worksheets in it. Each worksheet
will have the name of a person. As cases are added to the four sub
spreadsheets and names are assigned to the various cases I want this
information to then automatically populate the master spreadsheet. For
example, in the zone variance sub spreadsheet Oliver will be given a zone
variance case. The fields in the zone variance sub spreadsheet will include
the name of reviewer (i.e. Oliver), case number, date submitted, date due,
and probably a few other fields. As this information is added to the zone
variance spreadsheet I want it to automatically populate the master
spreadsheet under the worksheet "Oliver".

What formula(s) can I use in Excel to make this happen? I am able to use an
if-then statement to transfer the information right now, but its static. I
need to create a formula that will add the next "Oliver" case from any of the
four sub spreadsheets to the next blank row under the worksheet "Oliver" in
the master spreadsheet.

Thanks for your help!

 

[joel]

This is really a job for a VBA (Visual Basic) macro. Excel has a programming
language that you can get to from the worksheet by typing ALT-F11. I can
write a macro if you want. This is what I recommend

1) Create a summary workbook where you place the macro. Create a worksheet
called Template. Put in Row 1 starting in column B the header Row. The
header row will contain the order you want the summary sheet to look like.
The headers has to exactly match the headers in each of the other workbooks.
The macro will give you a warning message if the headers do not match.

1) Put all the workbooks you want in the summary in a single foler on you
PC. The macro will then open up each workbook and each sheet in the workbook


2) Column A is the key (person name) starting in Row 2 in all workbooks.

3) Each worksheet/workbook has a header row. The header rows in each
workbook/worksheet can be different.


4) The macro will go through each workbook in the folder going down column
A. It will check if there is a wroksheet in the summary workbook with the
person name. If Not is will add a new worksheet in the summary workbook for
each person. Then it will match the header rows in the old wrokbook to the
new workbook and put the data in the correct columns.

 

[Herbert Seidenberg]

Excel 2007
Advanced Filter, Macro.
Filters multiple Tables into one Table.
Separate tabs for all Tables.
Example uses a single workbook.
http://rapidshare.com/files/225805908/04_24_09.xlsm

 

[Herbert Seidenberg]

Alternate file host:
http://www.mediafire.com/file/5dzwomgndjn/04_24_09.xlsm

 

[Ed]

thanks Joel. I'll play around with visual basic and see who far I get.

 

[Ed]

Herbert - thank you for your leads. It looks like I need to spend some time
wrestling with visual basic.