Скачать или смотреть Excel - Summarize Total Row from Each Worksheet - Episode 1823

Microsoft Excel Tutorial: How to Build a Summary Table with Different Total Rows in Excel.

Welcome back to the MrExcel netcast! In today's episode, we will be discussing a question from David in my Fort Meyers seminar. David had a series of twelve different worksheets, each with a TOTAL row at the bottom. He was trying to create a summary table that would pull the TOTAL row from each worksheet. This seemed like a daunting task, but we have a solution for you.

In yesterday's episode, we showed you how to use the INDIRECT function to grab a specific row from each worksheet. However, in today's scenario, the TOTAL row is in a different location on each sheet. So, we will be using a combination of the VLOOKUP and INDIRECT functions to achieve our desired result. This is a great example of how to use these functions together to solve a common problem.

First, we will build a regular formula that cannot be copied down. We will use the VLOOKUP function to look up the word "TOTAL" on each sheet and choose columns A through H. Then, we will use the COLUMN function to specify which column we want to pull the data from. However, since the TOTAL row is in a different location on each sheet, we will need to use the INDIRECT function to dynamically reference the correct sheet. This may sound complicated, but we will break it down step by step in the video.

We will also address a comment from our previous episode, where a viewer suggested using the R1C1 notation in the INDIRECT function. This is a great tip and we will show you how to use it in today's episode. And, in tomorrow's episode, we will have another example of using INDIRECT, but this time we will show you how to avoid using it when it is causing your spreadsheet to slow down.

Thank you for tuning in to today's episode of the MrExcel netcast. We hope this solution helps you in your own Excel projects. Don't forget to check out our sponsor, MrExcel.com, and be sure to join us for our next episode where we will continue to share valuable Excel tips and tricks. See you next time!

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-...


Table of Contents:
(00:00) Total Row from Several Spreadsheets
(00:30) Introduction to the Use of INDIRECT
(00:40) Difficulty with TOTALs in Different Rows
(00:58) Use of INDIRECT for the Second Argument of VLOOKUP
(01:08) Building the Reference with CONCATENATE
(01:45) Testing the Formula
(01:57) Use of INDIRECT Again
(02:07) Building the Reference with CONCATENATE
(02:46) Use of INDIRECT to Get TOTALs from Different Sheets
(03:14) Introduction to Tomorrow's Episode 1824
(03:24) Different Example of INDIRECT
(03:44) Use of MID Function with COLUMN
(04:05) Explanation of the R1C1 Notation
(04:33) 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:
Building a summary table
Formula copying
Grabbing a total row
Improving spreadsheet performance
INDIRECT function
R1C1 notation
Summary table
VLOOKUP function
Worksheet expenses

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...



David from Fort Myers has one worksheet for each expense category. The total row appears on a different row in each worksheet. On a Summary worksheet, he would like to grab the totals from each worksheet. In today's episode, how to use INDIRECT as the 2nd argument in VLOOKUP to solve the problem.