So, I have a relatively complex request:
I have a number of workbooks (probably about 100) that represent indexes of documents. I would like to sync the information from these workbooks to a set of 'master' workbooks that will reflect the information from each of the smaller workbooks.
The names of the smaller workbooks are formatted thusly:
###-### (Name) Disc Index
The first "###" represents the client number, the second "###" represents the matter number (basically, the case).
The master workbooks will be divided by client number (one 'master' workbook per client). The name for these workbooks will follow this format:
### Disc Index
The "###" is the aforementioned client number.
In the smaller workbooks, there are two worksheets each with information on them (there is a third, but it doesn't contain any relevant information for this project).
The first worksheet contains information sorted into the following columns:
The second worksheet contains virtually identical columns with one column (Response Date) from the first worksheet split into two columns (Sent to Client and Responses Sent) in the second worksheet.
The first worksheet is named "DEF Disc" the second is named "PLTF Disc".
In the master workbook, there will be two worksheets that reflect the two worksheets present in each of the smaller workbooks (DEF Disc and PLTF Disc). Naturally, I would like for the information from all of the smaller workbooks' DEF Disc worksheets to appear on the master workbook's DEF Disc worksheet and the information from all of the smaller workbooks' PLTF Disc worksheets to appear on the master workbook's PLTF Disc worksheet.
Additional information: the smaller workbooks are quite simple and have a header row (1) and then the information immediately follows. However, the smaller workbooks do have their information contained in lists. I intend to add formatting to the final product (the 'master' worksheets), so it would be great if column A and rows 1 through 5 could be left blank. (If necessary, I could reflect these blanks cells (the one column and the five rows) on the smaller worksheets.)
If possible, please design the 'master' worksheets in such a way that it will be relatively easy to add additional smaller worksheets in the future (after all, the firm does occasionally take on new cases
).
Any help will be much appreciated! Feel free to ask me any questions.
Unfortunately, I cannot mail out these documents as they contain sensitive information (this is work for a law firm); however, if necessary, I can create a 'fake' set of smaller workbooks and a master workbook with random information.
I have a number of workbooks (probably about 100) that represent indexes of documents. I would like to sync the information from these workbooks to a set of 'master' workbooks that will reflect the information from each of the smaller workbooks.
The names of the smaller workbooks are formatted thusly:
###-### (Name) Disc Index
The first "###" represents the client number, the second "###" represents the matter number (basically, the case).
The master workbooks will be divided by client number (one 'master' workbook per client). The name for these workbooks will follow this format:
### Disc Index
The "###" is the aforementioned client number.
In the smaller workbooks, there are two worksheets each with information on them (there is a third, but it doesn't contain any relevant information for this project).
The first worksheet contains information sorted into the following columns:
- Name (the case name from above; the second "###")
- Number (the case number; both the first and second "###" separated by a dash, a unique value)
- Propounding Party (self-explanatory)
- Responding Party (self-explanatory)
- Type (the type of the document, a drop-down value)
- # (the number of the document, not a unique value)
- Date (the date on which the document was received)
- Method (the method(s) by which the document was delivered, a drop-down value)
- Response Date (the date the response to the document was received)
- Method (the method(s) by which the response was received, a drop-down value)
- Verification (whether or not a verification has been received, a Yes/No drop-down value)
The second worksheet contains virtually identical columns with one column (Response Date) from the first worksheet split into two columns (Sent to Client and Responses Sent) in the second worksheet.
- Name (the case name from above; the second "###")
- Number (the case number; both the first and second "###" separated by a dash, a unique value)
- Propounding Party (self-explanatory)
- Responding Party (self-explanatory)
- Type (the type of the document, a drop-down value)
- # (the number of the document, not a unique value)
- Date (the date on which the document was received)
- Method (the method(s) by which the document was delivered, a drop-down value)
- Sent to Client (the date the document was forwarded to the client)
- Responses Sent (the date the responses were sent out)
- Method (the method(s) by which the response was received, a drop-down value)
- Verification (whether or not a verification has been received, a Yes/No drop-down value)
The first worksheet is named "DEF Disc" the second is named "PLTF Disc".
In the master workbook, there will be two worksheets that reflect the two worksheets present in each of the smaller workbooks (DEF Disc and PLTF Disc). Naturally, I would like for the information from all of the smaller workbooks' DEF Disc worksheets to appear on the master workbook's DEF Disc worksheet and the information from all of the smaller workbooks' PLTF Disc worksheets to appear on the master workbook's PLTF Disc worksheet.
Additional information: the smaller workbooks are quite simple and have a header row (1) and then the information immediately follows. However, the smaller workbooks do have their information contained in lists. I intend to add formatting to the final product (the 'master' worksheets), so it would be great if column A and rows 1 through 5 could be left blank. (If necessary, I could reflect these blanks cells (the one column and the five rows) on the smaller worksheets.)
If possible, please design the 'master' worksheets in such a way that it will be relatively easy to add additional smaller worksheets in the future (after all, the firm does occasionally take on new cases
).Any help will be much appreciated! Feel free to ask me any questions.
Unfortunately, I cannot mail out these documents as they contain sensitive information (this is work for a law firm); however, if necessary, I can create a 'fake' set of smaller workbooks and a master workbook with random information.
