Summarising multiple worksheets onto one sheet

[Anon]

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]

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]

Thanks Dave, that's really helpful. Will give that a go.