You can also get some fantastic results when you combine it with named ranges.
So for example, we're going to see in the video how we can create cascading,
drop down lists, very easily, just using the INDIRECT function.
>> Now, I always had this discussion about the INDIRECT function being powerful, and
then others say that the INDIRECT function is just evil.
What's the context of this debate?
>> The truth is, it's both.
It is really powerful and there are some problems that could be more easily solved
with INDIRECT rather than other functions.
But it has two very serious drawbacks which we need to be aware of.
The first is is what's called a volatile function.
Excel, by default, uses smart recalculation.
So when you change something in a workbook, it works out which cells
are going to be impacted by that change and only recalculates those formulas.
But where you have a volatile function, they get recalculated regardless.
So if you have a lot of these functions in a large workbook,
you might see a real performance impact.
The other problem with the INDIRECT is it's hard to audit.
Because of its very nature, letting you specify where the inputs are coming
from on the fly, it's very hard to detect when an error creeps in.
So those are the two reasons why you might want to think about staying away
from INDIRECT.
>> Now, I often see the INDIRECT function being used with the ADDRESS function.
Will we be looking at that?
>> Absolutely, the ADDRESS function allows us to construct an address and that can
be an R1C1 or an A1 style, so it's a natural partner for the INDIRECT function.
And we're also going to look at some little helper functions that often go
alongside them and that's the ROW, ROWS, COLUMN, and COLUMNS functions.
>> And then, later on in the week,
we're going to be looking at the OFFSET function.
>> Absolutely, and the OFFSET function is a really powerful function, but
quite simple, really.
What it does is given a specified cell reference, it will return a reference or
range, a certain number of rows or columns away from our original starting point.
So it has great lookup potential, but
what it's mostly used for is creating dynamic ranges.
And from this, you can actually create dynamic charts and
quite a lot of other applications, so really useful function as well.
>> Any downsides that our learners need to be aware of with the OFFSET function?
>> Unfortunately, yes, similar to the INDIRECT, the OFFSET is also volatile, so
probably best to avoid it with large data sets.
And it also has auditing problems in that if you were, for example,
to trace precedence, it will only return the original reference.
So it has its drawbacks.
>> So some advanced yet very useful functions in Excel for looking up.
Thank you so much, Nicky.
Now, we've got some practice videos coming up for you.
Make sure you check them out and download the Excel workbooks, so
that you can work alongside us step by step.
Check out this week's quizzes, the practice challenge, the tool box,
as well as this week's great ninja tip.
Now, it's over to you.