Скачать или смотреть Excel - Indirect Address - Episode 602

Microsoft Excel Tutorial - Indirect Address.

Welcome back to the MrExcel netcast! In today's episode, we will be discussing the Indirect Address function in Excel. This topic was inspired by a question from Rod, who attended our Power Analyst Boot Camp in Chicago last week. Rod had a unique situation where he needed to selectively grab numbers from different worksheets based on a value chosen by the user. It took us some time to figure out the solution, but we finally got it working and I'm excited to share it with you.

Rod's scenario involved a series of different workbooks for different departments. In my example, I have six departments - Apple, Banana, Cherry, Eggplant, Grapes, and Kale - and a summary page. Rod wanted to be able to choose a department from a drop-down list and have the formulas on the summary page automatically grab values from that specific worksheet. The solution to this problem is the Indirect function.

The Indirect function allows you to refer to a specific cell by using a calculation or text value. For example, if you want to grab the value from cell F2, you can use the Indirect function and specify the row and column using the Row and Column functions. This is helpful because when you copy the formula down, the row and column references will adjust accordingly. But how does this help us in Rod's situation?

Well, in order to build a link back to another worksheet, we need to use the Indirect function in a slightly different way. We start by typing the equal sign, then selecting the worksheet name, followed by an exclamation point and the cell reference. However, if the worksheet name has a space in it, we need to wrap it in apostrophes. This is where the Indirect function comes in. We use the Indirect function to build the worksheet name and cell reference dynamically, based on the value chosen by the user.

In Rod's case, he had a data validation list where the user could choose a department. The Indirect function uses this value to build the worksheet name and cell reference. We also used the Address function to specify the row and column of the cell we wanted to grab the value from. This way, when the user chooses a different department, the formula will automatically update to grab the value from the corresponding worksheet. It's a very cool and efficient way to create a summary report that dynamically pulls data from different worksheets.

I hope this video was helpful in understanding the Indirect Address function in Excel. It's a powerful tool that can save you a lot of time and effort in situations like Rod's. Thank you for watching and don't forget to subscribe to our channel for more Excel tips and tricks. See you next time on the MrExcel netcast!

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) Introduction
(00:18) Question from Rod
(00:59) Using Indirect Function
(01:46) Building a Link to Another Worksheet
(02:52) Handling Spaces in Worksheet Names
(03:30) Formula for Rod's Question
(03:55) Evaluating the Formula
(04:05) Creating a Summary Report
(04:24) Common Mistakes with Indirect Function


#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:
Concatenation in Excel formulas
Creating a summary report in Excel
Creating links to other worksheets in Excel
Evaluating formulas in Excel
Handling worksheet names with spaces in Excel
How to selectively grab numbers from different worksheets in Excel
MrExcel netcast tutorial on INDIRECT function
Tips for using INDIRECT function in Excel
Using data validation with a list in Excel
Using the ADDRESS function in Excel
Using the INDIRECT function to grab values from specific worksheets
YouTube tutorial on using the INDIRECT function

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

Rod asks how to set up a summary worksheet that will selectively point to different worksheets. The solution is the INDIRECT function in combination with some other functions. Episode 602 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!