Creating a Master Summary Page

[JMcAnarney]

Question,

I am doing project management in excel and each page (69 sheets) is dedicated to a specific project. How can I create in a quick manner a way to gather information from all these different sheets in hopes of forming one large data set to then create a pivot table?

My data would contain both dollar amounts along with multiple word classifications such as funding source, contact person, etc. I could use the consolidate function, but that does not allow words to be incorporated into it, and sometimes the consolidate function sums items you do not want summed. For example, it sums if the project name is the same, but has different funding source which is what I do not want if I am doing a PIVOT Table. In the attached file highlighted in green is how the data should look based off references, and what I want to do is have them all on one large data page without doing it the long way of copy and paste.

I could do the long way and do cell references but that would be really tedious.

Any work arounds?

 

[RasGhul]

I would recommend opening a new workbook for the large data set, use Power Query to append all sheets in one go with your original workbook as your source.

Consolidate Excel Sheets with Power Query