Forum Discussion
Selecting one cell from multiple sheets for summary page
Hi everyone,
I'm sure there is an easy answer to this, but I just can't seem to find this answer through search.
I have about 50+ sheets that are all the same format. I want to create a summary page that takes everything on each tab(sheet) and displays the values in cell C31. For example my summary page will look like this. The values in B would be from sheet 1:cell C31. I can do this the hard way which is go and just select each one, but I'm wondering if there is any easier way to do this.
| A | B |
|Sheet 1 | C31 |
|Sheet 2 | C31 |
|Sheet 3 | C31
With the sheet names in A2, A3 etc., enter the following formula in B2, then fill down:
=INDIRECT("'"&A2&"'!C31")
4 Replies
Additional possibilities and methods are to use Power Query Editor and PivotTable.
You can also use 3D Reference or Consolidate Command to create a summary table from multiple worksheets.
Here’s how you can use Power Query Editor and PivotTable:
- Select any cell in your data range.
- Go to Data > Get & Transform Data > From Table/Range.
- In the Power Query Editor window, select all columns except column C31.
- Go to Transform > Unpivot Columns > Unpivot Only Selected Columns.
- Close and Load the query to a new worksheet.
- In the new worksheet, go to Insert > PivotTable > OK.
- In the PivotTable Fields pane, drag Sheet column to Rows area and Value column to Values area.
- In the PivotTable, right-click Sheet field and select Group > By > OK.
- In the Grouping dialog box, select By Sheet and click OK.
Additional Link & methods: Excel creating a summary from multiple tabs in a workbook
I hope this helps!
Thank you for that detailed answer. Learning more in excel has been one of the tasks I keep putting off however it's one of those things that just makes life easier. Your example and details I'm sure sound like something I'm going to need to learn and thank you for providing them. Next on my list is pivot tables for learning! Much appreciated
With the sheet names in A2, A3 etc., enter the following formula in B2, then fill down:
=INDIRECT("'"&A2&"'!C31")- Rockstar! Thank you so much. Worked like a charm!
