Excel Indirect Function Tutorial

Welcome to Tiger's Excel Indirect function tutorial. The Indirect formula has super powers that could be a game-changer for you. This example is inspired by a recent work situation where the formula saved me a huge amount of time.

Download file link:

How could we summarise Indirect's 'magical powers'. Well, let's suppose you wish to apply a formula to multiple sheets. In my example, I wish to see how many times data appears on datasets from different US states. We can use Countif to do this, as I demonstrate in the video. And, voila! The analysis is complete.

So, what's the problem? The problem is that we need to apply the same analysis to more than one sheet. In the example we have four sheets, but you might have fifty, or more. Can you imagine building the formula fifty (fifty!) times, in order to apply it to different sheets. This would be intensely frustrating!

The fact is that there are too many people out there trying to do this kind of manual task in Excel. I wish a few more of them watch the Tiger channel :-)

This is where Indirect changes the game. Indirect converts the contents of a cell into a text string and allows us to use the text string in a formula. Such a technical exposition does not sound exciting; so, how else can we express it? How about 'Indirect allows you to input sheet names into column headers, and to directly reference the sheet names in formulae'?

Perhaps this sounds more exciting. No matter, Chris demonstrates how the formula works in the video. Correctly implemented (the formula is notoriously tricky to work with), Indirect allows us to control the sheet that a formula looks at by simply changing the value in a cell. Crazy! And incredibly cool.

The implications are significant. I managed to speed up a task that would have taken most an afternoon, and complete it in just a few minutes.

Did you manage to complete the task in the video? What have you managed to accomplish using the Indirect function? Leave a comment below the video, Chris will get back to you.
---------------------------------------------------------------
🎁YOUR EXCEL CHEATSHEET
🎁1 Page Summary Of What You Need To Know

Also on Insta :-)

[kenjifox]

After a lot of research, I finally figured out that the proper use of the INDIRECT function is to avoid confrontation.

[chenjingyu]

Finally I knew how to use Indirect function, Thanks to this video!

[deninsrmic]

Thank you for puting it right to the point with a lots of details.Great Video on =INDIRECT("'"&Function&"'!").

[shferaw]

I really appreciate your help, thank you very much! On my excel I have Purchase & Sale column together. Purchase can be in cash & in Credit & also the same way Sale can be in cash & in Credit. Now I need to get total credit in purchase & total credit in Sale. The same to credit for sale. Can I do this with this function?? Or shall I send you please?

[MrVenterW]

Thanks Chris, your explanation helped alot to get better understanding. I used your database and changed to tables which add some value.

[thanossaxinidis]

Thanks for the great video. Please, I have a question for you. I have a worksheet with two sheets, “Sheet1” and “Sheet2”. In a function, i want to open a sheet, “Sheet1” or “Sheet2”, by taking the value from a cell, for example the cell D1. In the function "LOOKUP (A36; x $ A $ 10: $ G $ 1000; x $ E $ 10: $ E $ 1000)" i want the variable "x" to take a value from the cell “D1” in order to open either “Sheet1” or “Sheet2” (its up what is in cell “D1”) and after looking at $ A $ 10: $ G $ 1000 with the price of A36, give me the price of E. Thank you.

[kendrewt]

Great! Thank you! Simplicity out of complexity

[ubaidillahmuhammad]

Good job. But please given the file Sir

[BlvdMinSS]

Thanks Chris so much for this video....I now can make this happen in my work!

[c.e.bingham]

Thank you Chris! I had heard others speak of using this function but I could never figure out how to make it work. Your example is similar to a problem that I’m having and now I understand how to use INDIRECT as a solution for me.

[BlvdMinSS]

Chris, can I couple this with VBA to pull all the rows (the entire row of data) of data with names that appear on 3 or more sheets into a new sheet, that has all the data for that name(that row)?

[dads_random_projects]

I use this formula a lot for referencing other sheets dynamically like you demonstrated in your video. My challenge is that it is VERY inefficient and when used frequently within the same workbook. It can really slow excel down to a point where it is almost unusable. I look forward to the day that there is a much more efficient formula that replaces this one or it is upgraded to make it a non-volatile formula. Good video.

[jacquelinerobinson]

After watching and reading at least a dozen INDIRECT tutorials, this is the first one that didn’t give me a #REF error immediately. Thank you so much!

[kamieldraulans]

Hello, very interesting video indeed, thank you! I was wondering if it was possible to use it to reference to another workbook?

[Darko]

Well done Chris, you've explained a complicated formula in a simple way so that anyone can understand. This is the hallmark of a great teacher.


Best wishes,
Darko

[maurocastagnera]

it's amazing how many things you can do with a computer but the most incredible thing is to see how many clever and intelligent people like you can create all this, because if we think about it, computers are just so many pieces of iron assembled together. Bravo Chris congratulations! 😊👍

[buttzilla]

Chris, for me the INDIRECT FORMULA has always given me a load of trouble !!! Thank you for the quick tutorial in how to go about using it. After an explanation video like this, I feel more confident the next time I attempt to use INDIRECT and it will become something I use more often, rather than looking into alternatives that could be more complicated and less effective. Another Great Video, thanks again :)