Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summarising multiple worksheets onto one sheet
Please forgive me as I'm rather unfamiliar with Excel jargon. I'm using Excel
2003 and would like some help on how to summarise multiple worksheets onto one master sheet within the same workbook. Basically the workbook I'm trying to create will have a separate sheet for each division of the department where I work, outlining project work for that division. The data will be purely text. Although the content will vary for each sheet, the headings will be the same. I would like the information from each sheet to automatically and continuously be copied over to a master sheet (ideally Sheet1) so that project work across all divisions can be viewed altogether on demand. Ideally, once this has been set up, if the master sheet were to be updated, the relevant cell on the respective divisional sheet would be updated as well, and vice versa. Thanks in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summarising multiple worksheets onto one sheet
This is pretty much impossible using excel. You can try to use macros, but
those could be broken by the user not enabling macros--or disabling events. I wouldn't even try. I'd set up one sheet to do all the data entry. Then I'd use Data|Filter|autofilter to be able to filter the data to just display the rows I wanted to see. It makes updating the data much, much safer. If I had to have separate sheets per division, then I'd create "report" worksheets that would be used for viewing--not updating. All the updates would still go into the master worksheet. If you think you'd want to try this kind of thing... Ron de Bruin's EasyFilter addin: http://www.rondebruin.nl/easyfilter.htm Code from Debra Dalgleish's site: http://www.contextures.com/excelfiles.html Create New Sheets from Filtered List -- uses an Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.xls 35 kb Update Sheets from Master -- uses an Advanced Filter to send data from Master sheet to individual worksheets -- replaces old data with current. AdvFilterCity.xls 55 kb If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Anon wrote: Please forgive me as I'm rather unfamiliar with Excel jargon. I'm using Excel 2003 and would like some help on how to summarise multiple worksheets onto one master sheet within the same workbook. Basically the workbook I'm trying to create will have a separate sheet for each division of the department where I work, outlining project work for that division. The data will be purely text. Although the content will vary for each sheet, the headings will be the same. I would like the information from each sheet to automatically and continuously be copied over to a master sheet (ideally Sheet1) so that project work across all divisions can be viewed altogether on demand. Ideally, once this has been set up, if the master sheet were to be updated, the relevant cell on the respective divisional sheet would be updated as well, and vice versa. Thanks in advance! -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summarising multiple worksheets onto one sheet
Thanks Dave, that's really helpful. Will give that a go.
"Dave Peterson" wrote: This is pretty much impossible using excel. You can try to use macros, but those could be broken by the user not enabling macros--or disabling events. I wouldn't even try. I'd set up one sheet to do all the data entry. Then I'd use Data|Filter|autofilter to be able to filter the data to just display the rows I wanted to see. It makes updating the data much, much safer. If I had to have separate sheets per division, then I'd create "report" worksheets that would be used for viewing--not updating. All the updates would still go into the master worksheet. If you think you'd want to try this kind of thing... Ron de Bruin's EasyFilter addin: http://www.rondebruin.nl/easyfilter.htm Code from Debra Dalgleish's site: http://www.contextures.com/excelfiles.html Create New Sheets from Filtered List -- uses an Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.xls 35 kb Update Sheets from Master -- uses an Advanced Filter to send data from Master sheet to individual worksheets -- replaces old data with current. AdvFilterCity.xls 55 kb If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Anon wrote: Please forgive me as I'm rather unfamiliar with Excel jargon. I'm using Excel 2003 and would like some help on how to summarise multiple worksheets onto one master sheet within the same workbook. Basically the workbook I'm trying to create will have a separate sheet for each division of the department where I work, outlining project work for that division. The data will be purely text. Although the content will vary for each sheet, the headings will be the same. I would like the information from each sheet to automatically and continuously be copied over to a master sheet (ideally Sheet1) so that project work across all divisions can be viewed altogether on demand. Ideally, once this has been set up, if the master sheet were to be updated, the relevant cell on the respective divisional sheet would be updated as well, and vice versa. Thanks in advance! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I tie multiple worksheets to one sheet in Excel? | Excel Worksheet Functions | |||
Collating/ Summarising worksheets | Excel Discussion (Misc queries) | |||
Consolidate multiple worksheets into new sheet | Excel Worksheet Functions | |||
Summarising Numerous Worksheets | Excel Discussion (Misc queries) | |||
Summary sheet for multiple worksheets | Excel Discussion (Misc queries) |