Mar 26 2023 08:36 AM
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
Mar 26 2023 08:52 AM
SolutionWith the sheet names in A2, A3 etc., enter the following formula in B2, then fill down:
=INDIRECT("'"&A2&"'!C31")
Mar 26 2023 09:20 AM
Mar 26 2023 09:50 AM
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:
Additional Link & methods: Excel creating a summary from multiple tabs in a workbook
I hope this helps!
Mar 26 2023 10:23 AM
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
Mar 26 2023 08:52 AM
SolutionWith the sheet names in A2, A3 etc., enter the following formula in B2, then fill down:
=INDIRECT("'"&A2&"'!C31")