Mar 19 2022 06:35 PM
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
Mar 20 2022 03:10 PM
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. https://exceljet.net/search?query=INDIRECT
Mar 22 2022 01:52 PM
@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.