List data from all tabs or worksheets into columns in a summary table

New Contributor

I have looked for the solution but always get answers that use a function (such as adding values together).  What I want to do is create a list/column of the data from multiple tabs or worksheets into a summary master table.


I have a large workbook, each tab  is the same worksheet format with different project data in it.  The format means that Cell C3 is always "Project #" D3 would be "Project Name" and so on.  What I want is a summary table that lists all of the information from the worksheets/tabs into a column.  In this case, if there are 100 tabs, the column labeled "Project Number' in the summary table would have 100 entries. 

I was able to manually reference each item, but with such a large workbook it becomes cumbersome.  There must be an automated way to do this.  I thought the Consolidate function might work, but it always seems to want to perform a math function and enter the data into a single cell.  I simply want to list the data into a column.


Any tips appreciated.

Thank you


2 Replies



From your description, I believe the INDIRECT function is what you might need. I use it under similar circumstances to summarize data from across multiple sheets/tabs within a larger workbook.


I'm attaching two examples of the use of INDIRECT. One of those (INDIRECT_example) constructs the tab name using the word "Sheet" and a variable number. The other is more straightforward. 


Here, too, is a website that explains it more thoroughly.


@mathetesThanks for the response, I will dig into this.  If I can get it to work, you may have saved me a lot of repetitive actions.  Much appreciated either way.