Bringing data from multiple sheets to the summary page

[ExcelRookie]

I am using excel for project management. I would like the ability to bring
data from multiple sheets to the summary page. For example, I would like the
ability to find out, on demand with just a click, all the tasks that a
particular person is working on from multiple sheets. Can anyone please
let me know how best to resolve this? Thanks much!

 

[Bernie Deitrick]

You can resolve it by turning your thinking around. Don't use multiple sheets: use one data base,
where all the information is entered into a table, and then create the reports from that using
filters, pivot tables, or formulas that extract specific information. Just because you can use
multiple sheets does not mean it is a good idea in all cases.

HTH,
Bernie
MS Excel MVP

 

[ExcelRookie]

Thanks for the feedback. Which database do you mean - Excel or Access? If
Excel, can you please send me a link to Excel template to maintain tasks and
projects.

 

[ryguy7272]

Look here for a macro to 'bring data from multiple sheets to the summary
page', per your request:
http://www.rondebruin.nl/summary.htm

This is excellent too (also by Ron):
http://msdn.microsoft.com/en-us/library/cc793964.aspx


Regards,
Ryan---

 

[Bernie Deitrick]

A database is just a table with a specific structure. Let's say that all
the sheets you were set up for individual projects (prhaps, by using project
names as the tab name), with WORKER, TASK, and HOURS values stored on
each sheet. Instead of multiple sheets, you could then set up one table
with

PROJECT WORKER TASK HOURS

where you entered those four items for every worker - then you could use a
pivot table to sum the hours by TASK, by WORKER, by PROJECT, by TASK and by
PROJECT, etc.

HTH,
Bernie

 

[ExcelRookie]

Thanks for the links, Ryan. They were very helpful.