Auto-populating a summary sheet from multiple worksheets

Helen Wills

New Member
Nov 14, 2014
Office Version
  1. 365
  1. Windows

I am building a Project managing tool to support the delivery of 70+ simultaneous projects. Each project has its own worksheet.
On each worksheet I have some core information which i for which i have a summary tab

However, each project sheet also has space for a list of required tasks. The number of tasks will vary by project and will all be populated at different times - The task lists are to be found from Cells C49 -C78 on each tab

I want to build a separate 'task log' - a summary sheet that collates all tasks from all sheets - so that i can filter and sort by date to see what is due / overdue.
How can i do this so that each new task is automatically pulled through on to the summary sheet? Tasks will be added at different times in the year and the number of tasks will vary per project - therefore I don't want to create a summary sheet with static rows that will be blank until there is data - i want tasks to be copied over to the summary tab only when they appear on a project sheet and be added to the list in turn

- 'Project sheet 1' has 15 tasks listed in between cells C49 - C63 (rows C64 - C78 are still empty)
- Project sheet 2' has no tasks listed yet
- 'Project sheet 3' has 2 tasks listed in between cells C49 - C50 (rows C51 - C78 are still empty)
- I'd like the summary sheet to show the data in 17 consecutive rows from Project Sheet 1 and Project Sheet 2. Then, if any data is added at a later stage it would fill up underneath the original 17 tasks
- I'll still need to be able to apply a filter / sort the data so any solution needs to ensure this can still be done

Not sure if this makes sense, but any help you could give would be appreciated...

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Watch MrExcel Video

Forum statistics

Latest member