Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
linking between spreadsheets
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! -- Ed |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
linking between spreadsheets
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. "Ed" wrote: 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! -- Ed |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
linking between spreadsheets
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
linking between spreadsheets
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
linking between spreadsheets
thanks Joel. I'll play around with visual basic and see who far I get.
-- Ed "joel" wrote: 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. "Ed" wrote: 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! -- Ed |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
linking between spreadsheets
Herbert - thank you for your leads. It looks like I need to spend some time
wrestling with visual basic. -- Ed "Herbert Seidenberg" wrote: Alternate file host: http://www.mediafire.com/file/5dzwomgndjn/04_24_09.xlsm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Spreadsheets | Excel Worksheet Functions | |||
linking spreadsheets | Excel Worksheet Functions | |||
Linking two spreadsheets | New Users to Excel | |||
Linking Spreadsheets | Excel Worksheet Functions | |||
Linking Spreadsheets | Excel Worksheet Functions |