Look Who it Was Random video

Excel - Excel Formula to Point to a Certain Cell on Each Worksheet Using INDIRECT - Episode 819

1,677 views · Published 12 January 2009 · 2:52 · Indexed 23 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Excel Formula to Point to a Certain Cell on Each Worksheet Using INDIRECT.

Welcome back to the MrExcel netcast! In today's video, we're going to tackle an interesting spreadsheet setup where we have 31 worksheets in one workbook, each representing a different day. Our goal is to pull data from a specific cell on each worksheet and display it on our SUMMARY tab. Sounds complicated, right? Well, not with the help of the INDIRECT function.

First, we'll use the TEXT function to format the date in a way that matches the sheet name. This will make it easier for us to reference the correct sheet. Then, we'll use the CONCATENATE function to combine the formatted date with the cell reference we want to pull data from. This will give us a cell address that looks like the one we want, but with one small problem - the spaces in the sheet name. Don't worry, we'll take care of that too.

Now, here comes the magic - the INDIRECT function. This function allows us to reference a cell using a text string. So, we can use the cell address we created earlier as the argument for the INDIRECT function and voila! We have a formula that dynamically pulls data from different worksheets based on the date. And the best part? It updates automatically as the date changes.

But, as with any function, there are limitations. In tomorrow's netcast, we'll explore how to use INDIRECT to pull data from an external workbook, which is a bit trickier. So, make sure to tune in for that. And as always, thank you for watching and we'll see you next time for another netcast from MrExcel. Don't forget to like, comment, and subscribe for more Excel tips and tricks.

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/

You have a workbook with one sheet for each day in the month. A summary worksheet in the workbook needs to grab data from each worksheet based on the date in column A. Episode 819 will show you how to use INDIRECT to solve this problem.

Table of Contents:
(00:00) Introduction to the MrExcel netcast
(00:10) Spreadsheet setup with 31 worksheets
(00:22) Using the date to pull data from specific sheet
(00:32) Using the TEXT function to format the date
(00:43) Using the & and ! to create a cell reference
(01:16) Using the INDIRECT function to grab data from referenced cell
(01:49) Benefits and limitations of INDIRECT function
(02:23) Preview of tomorrow's netcast on using INDIRECT with external workbooks
(02: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:
Cell reference
CONCATENATE function
Data retrieval from different worksheets
External workbook data retrieval
INDIRECT function
Limitations of INDIRECT function
Live updating formulas
SALES data
Spreadsheet setup
TEXT function
Workbook with 31 worksheets

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

More from this channel