How to Continuing in the Indirect Cell Reference

Usually you type =A1 for referring to the cell A1 in Excel. But instead, there is also another method: You could use the INDIRECT formula. The formula returns the reference given in a text. So instead of directly linking to =A1, you could say =INDIRECT("A1"). In this article, we are taking a look at how to use the INDIRECT formula and why it is very useful. But there are also disadvantages.

Before we start: If you have already created an INDIRECT function and want to evaluate it, please refer to this article. It describes three methods to follow up INDIRECT formulas.

Contents

• Steps for applying the INDIRECT formula in Excel
• Example: Use INDIRECT for referring to another sheet
• Example: Use named ranges in INDIRECT
• Why is the INDIRECT formula useful?
• Disadvantages of the INDIRECT formula
  • Disadvantage 1: INDIRECT is Volatile
  • Disadvantage 2: Cell references not recognized by Excel
  • Disadvantage 3: INDIRECT is very unstable
• Conclusion
  • Also interesting:

Steps for applying the INDIRECT formula in Excel

Example for the INDIRECT formula in Excel.

You want to get data from different sheets but always on the same cell?

As said in the introduction, INDIRECT returns the value of a cell which you specify by a string. Please take a look at the picture on the right hand side. What will this formula return?

The function says

                      =INDIRECT(C5&D5)                  

C5 has the letter B in it and D5 the number 2. If you combine them with the & sign, it's B2. So if we step into this formula, it says =INDIRECT(B2). It refers to cell B2. As B2 contains the text "Test Cell", the formula will return "Test Cell" in cell B5.

---

Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Add more than 120 great features to Excel!

---

Example: Use INDIRECT for referring to another sheet

Let's take it a step further: Instead of just B2, you can also refer to other sheets, or even other workbooks. Let's assume we type this formula into Sheet2 but we want to get the value from cell B2 on Sheet1. So, we have to add the worksheet name. This could look something like this:

                      =INDIRECT("Sheet1!B2")                  

If you replace B2 with the cell references like in our picture, the formula will look like this:

                      =INDIRECT("Sheet1!"&C5&D5)                  

Of course, you can now replace the static text "Sheet1!" by another cell reference, which contains the text "Sheet1". Then you have to concatenate the cells including the ! in the middle. Please note that if your sheet names have spaces or special characters like "-" in them, you have to add ' before and after the sheet name:

                      =INDIRECT("'Sheet 1'!"&C5&D5)                  

Example: Use named ranges in INDIRECT

Instead of creating cell references with the cell location, you can also use named ranges. Let's assume you've named cell C6 "Tax_Rate" like in the following screenshot:

Now you can refer to this cell using =Tax_Rate.

Using the INDIRECT function, it works the same way as with normal cell references: =INDIRECT("Tax_Rate"). The name is not case sensitive, so it'll also work when you type =INDIRECT("Tax_rate").

Why is the INDIRECT formula useful?

Within the reference of INDIRECT, you can combine the cell link as complicated as you like. If you have several similar sheets, for example filled out surveys. You can easily put together the indirect formula always linking to the same cells on each worksheet.

Furthermore you can use it within your formulas:

                      =VLOOKUP(A1,INDIRECT("Sheet1!A:B"),2,FALSE)                  

There are countless ways of integrating INDIRECT into your formulas.

Disadvantages of the INDIRECT formula

Disadvantage 1: INDIRECT is Volatile

But there is are also disadvantages, especially in very large workbook: The INDIRECT formula is volatile. That means that Excel will calculate it again, each time when it calculates the whole workbook. Must other formulas will only be calculated depending on if their input values have changed. But INDIRECT will always be recalculated.

Disadvantage 2: Cell references not recognized by Excel

Another disadvantage is, that the reference is not recognized by a cell link. Let me give you an example: When you copy and paste a worksheet containing the INDIRECT formula, the break link function doesn't work. Instead, you will see the #REF! error.

Also, formula auditing functions are hardly working: You can't highlight the input cell with the "Trace Precedents" function. In the other direction it also doesn't work: The the "Trace Dependents" function you can check, if your current cell is an input value for another cell. If your cell is an input for the INDIRECT formula, it won't be detected.

Disadvantage 3: INDIRECT is very unstable

The formula is very unstable in terms of changing workbook structures. Most formulas adapt when you insert rows or columns. Or move cells.=A1 will adapt, when you move the cell A1. =INDIRECT("A1") on the other hand won't.

Conclusion

The INDIRECT formula can be very helpful. It allows you to dynamically change cell references. But it comes with major disadvantages so that you should consider well, if it is worth using it.

johnsonsument.blogspot.com

Source: https://professor-excel.com/indirect-formula/

Share this post