Excel - Summarize Worksheets Using INDIRECT - Episode 1822
2,034 views · Published 5 November 2013 · 4:59 · Indexed 26 October 2026
Channel: MrExcel.com · 2013 · Education
Microsoft Excel Tutorial: Summarizing Data from Multiple Worksheets with the INDIRECT Function. Welcome back to the MrExcel netcast! In this episode, we will be discussing how to summarize data from multiple worksheets using the INDIRECT function. This is a common issue faced by many Excel users, and we have received two similar questions from our viewers, Andrew Z and David from Fort Myers. So, we will be addressing this topic over two episodes. Andrew Z's question is about creating a table by pulling data from other sheets. He has tried using sheet names and references, but is unable to change the sheet name as he copies the formula down. We will be solving this problem in today's episode, 1822. Tomorrow, in episode 1823, we will tackle David's question, where the total row is in a different location on each worksheet. To demonstrate this issue, we have a workbook with multiple worksheets for different companies such as AT&T, CitiGroup, Exxon, Ford, General Electric, General Motors, and Wal-Mart. The total for each company is in row 1, and we want to create a summary table in a separate worksheet. The first step is to build the formula for the first company, AT&T. We use the INDIRECT function to analyze the cell reference and return the value. This function will help us solve the issue of changing sheet names as we copy the formula. To build the formula, we use the MID function to return the column letter that we are currently in. This will change as we copy the formula throughout the summary table. By using formulas to build a formula, we are being clever and efficient. This INDIRECT function will solve Andrew's problem as he is always pointing to the same row number. However, in David's case, the total row is in a different location on each worksheet, and we will address this in tomorrow's episode, 1823. Thank you to Andrew and David for sending in your questions, and thank you for tuning in to this episode of the MrExcel netcast. We hope this tutorial has been helpful, and we will see you next time for another netcast from MrExcel. Don't forget to check out our sponsor, MrExcel.com, for all your Excel needs. Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Introduction and Sponsorship (00:10) Two Similar Questions from Viewers (00:20) Andrew's Question: Creating a Table by Pulling Data from Other Sheets (00:32) Tomorrow's Episode: David's Question with a Twist (00:46) Example Worksheets (01:02) Building the First Formula (01:29) Using the =INDIRECT Function (02:41) Building the Cell Reference (03:33) Formula Explanation and Demonstration (04:48) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: Analyzing cell references in Excel Building a summary in Excel Changing sheet names in formulas Creating a table with data from multiple sheets Learn Excel from MrExcel Podcast Episode 1822: Summarize Worksheets With INDIRECT Pulling data from other sheets in Excel Solving formula problems with the INDIRECT function Using formulas to build formulas in Excel Using the INDIRECT function in Excel Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153643/ Andrew and David have similar Excel issues - they need a Summary worksheet to return values from all other worksheets and want a simple way to enter one formula that can be copied to point to the other worksheets. Today, in episode 1822, how to use INDIRECT to solve the problem when the Total row is on the same row in every worksheet.
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920