Excel INDIRECT Function-Lock Absolute Reference

In an exclusive World Movie Premiere, here is the first (and probably last) instalment in Excel Theatre. It’s an animated short, named Absolute Reference Problems. Watch for it in this year’s Oscar nominations!

Video: Absolute Reference Problems

Please note the giant spreadsheet in the background of the video below. I think that grid adds to the tension in this dramatic presentation.

Just so you know – the video’s dialog is corny, the actors are wooden, the plot is weak and the costumes are pitiful. Other than that, it’s pretty good. 😉

The INDIRECT Function

If you haven’t used INDIRECT before, it’s a formula that returns a reference to a range, based on a text string.

As the video pointed out, you can use an absolute reference to a cell, to “lock” the reference, and keep if from changing if you copy the formula to a different cell.

However, if the referenced cell moves, the absolute reference changes to match the new location.

Two Worksheet Formulas

For example, in the screenshot below:

  • cell C2 contains an absolute reference to cell A1
  • cell C3 contains an INDIRECT formula that refers to cell A1.
absolute reference to cell A1
absolute reference to cell A1

Insert Row Above

If you insert a blank row at the top of the worksheet, the formula in cell C2 changes, and it now refers to cell A2.

However, because it’s a text string, the reference in the INDIRECT formula does NOT change. It returns a zero because cell A1 is now empty.

Indirect02

Using the INDIRECT Function

You can use INDIRECT in many ways. For example:

  • dependent data validation lists
  • to prevent a cell reference from being affected by a move
  • create cell references from a combination of cell values and text.

For more information on the INDIRECT function, and examples of how to use it, please visit the INDIRECT Function page on my website.

Video Transcript

In case you want to read along with the animated video characters, here is the full transcript. Have fun!

Oh No!

What is wrong?

I used an absolute reference to cell A1

Good! Your formula should always refer to that cell

I thought so too, but then I inserted a new row at the top of the worksheet

That should be okay

It isn’t! Now my formula refers to cell A2

Oh no! The total could be wrong now.

How could that happen? What is the point of using an absolute reference if it can change?

Maybe you should try an INDIRECT instead

D’oh! Why didn’t I think of that?

I will change it to an INDIRECT formula, and it will always refer to cell A1, thanks!

Video Note

How did I end up making this silly Excel video? Well, I should have been working all day, but decided to take a bit of time to relax and catch up on some reading (of RSS feeds).

On a technology blog, I saw a link to XtraNormal, where you can write, cast and direct an animated movie.

That sounded like more fun than working, so off I went.
________________

0 thoughts on “Excel INDIRECT Function-Lock Absolute Reference”

  1. Thanks Dick! I might abandon programming and become a director.
    Ken, this is art — you can’t measure it in time. 😉 I didn’t keep track, but it was probably about 1-1/2 hours. Most of that was experimenting with the tools for the first time. Once you’re familiar with them, it would be pretty quick. Just type the dialog, add a few camera and action tools, and publish it.

  2. I was not familiar with this function, and as useful as this new info is, it pales next to the cool video. Thanks!

Leave a Reply to bbouvier Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.