Indirect Function in Excel returns a reference to a range. Excel Indirect functions are useful to create a reference that won’t change if columns or rows are inserted in a worksheet. There are a number of indirect functions in excel such as SUM, MAX, MIN & Independent Cell Value. In this tutorial, we explain one way to use these indirect functions. To learn more ways, please check our latest **Excel Ninja Training with Certification**. indirect function in excel

**What is an INDIRECT Function in Excel?**

The **INDIRECT Function in Excel** helps you to return a reference to a range. Even when you insert Rows or Columns in a Worksheet the reference you created won’t change. This will help you to create a reference from letters and numbers in other cells.

**How INDIRECT Function in Excel Works with an Example**

The INDIRECT function is useful when you want to return a value, based on a text string.** Example:** You’ll be able to see the list of food items and their Consumption by Countries such as **USA, China **and **Japan**. The quantity is in Million i.e. **1 Million=10,00,000**

**Problem:** You need to find out:

- An
**independent Cell Value** **SUM**consumption of the Food items of USA**MAX**Value of Food Consumption of Japan**MIN**Value of Food Consumption of CHINA

Question: How will you find the Values?

**Solution: INDIRECT Formula** will help you get this in minutes

**INDIRECT Formula to find an independent Cell Value**

- In the Cell F3,
**Type =INDIRECT**(for ref_text give the reference of the above Cell, Select F2) i.e. =INDIRECT(F2) - You get
**#REF!**(This is because there was**no Cell Value assigned to the Cell F2**) **Select C5**and**Enter C5**as the**Cell Value**at the**place of F2**. You get**Value 150**

(The **Error text disappears** and you get the Value 150 i.e. the Value of Silk Consumption in China)

**INDIRECT Formula to find a ****Sum consumption**

**Select the range**of Cells for the Columns USA, CHINA and JAPAN- Provide a Table Name to USA, CHINA and JAPAN respectively in the
**NAME BOX** **Type the name USA**in the**Cell F2**- In the
**Cell F3**,**Type =SUM(INDIRECT(F2))**

**Note 1: ***Don’t forget to close the Bracket for SUM & INDIRECT both*

**Note 2:** *If you don’t Select the Table Range and Name your Table then Typing USA won’t give the SUM in Excel. You must Give your Table a name in the NAME BOX*

**Press Enter**. You’ll get the**Value 1250**

*(You can Cross-Check the Total by Calculating the Total Values of USA. The Answer remains same)*

**Note: ***You can change the Country Name to China and Japan Respectively to get the Sum of the Country*

**INDIRECT Formula to find**** MAX Value**

- In the
**Cell F2**,**Type JAPAN** - In the
**Cell F3**,**Type =MAX(=INDIRECT(F2))** **Press Enter**. You’ll get the**Value 450**

**Note:** *If you don’t Select the Table Range and Name your Table then Typing USA won’t give the SUM in Excel. You must give your Table a name in the NAME BOX*

(You can Cross Check the MAX Value by Checking out the Column of Japan. The Answer remains same)

**INDIRECT Formula to find ****MIN Value**

- In the
**Cell F2**,**Type CHINA** - In the
**Cell F3**,**Type =MIN(=INDIRECT(F2))** **Press Enter**. You’ll get the**Value 150**

**Note:** *If you don’t Select the Table Range and Name your Table then Typing USA won’t give the SUM in Excel. You must give your Table a name in the NAME BOX*

(You can Cross-Check the MIN Value by Checking out the Column of Japan. The Answer remains same)

**Wish to learn about:**

**‘Excel reference cell in another sheet dynamically’?**

Excel INDIRECT function – syntax and basic uses

As you understand from the Word, ‘Excel INDIRECT’, *it helps to indirectly reference cells, ranges, other sheets or workbooks. *

In other words, The **INDIRECT function in Excel** makes it easy for you to create an Excel dynamic cell reference or range reference. You do not have to do the “hard-coding” of the references into your Excel formulas.

This results in the change of a Cell Reference within a formula without changing the formula itself. More importantly, these indirect references in the INDIRECT Formula in Excel do not change when a new row or column is added or inserted in the worksheet. Also, the Cell Reference remains the same when you delete the existing Rows and Columns. When some formulas get extended you can use wrap text in Excel

If you wish to Write an INDIRECT Function in Excel, you need to understand the Basics of Function Arguments.

**What if your Indirect formula not working?**

**Situation: **You’re using the indirect formula in Excel to pull data of other tabs.

These tab names are compiled and placed on a tab through VBA code (this is a clear cut and paste)

**Problem:** The Indirect formula is unable to calculate because what I am assuming is the format of the data which is present in column B

**It is observed that: **Whenever you type over the data (which is exactly the same) this excel formula works.

**Solution: **Before you overwrite, you need to check:

- If there are unseen Whitespace Characters in the Cell
- If something is Misspelled

If this doesn’t work then, you can try this:

=IF(ISERROR(INDIRECT(TRIM($B2)&”!U2″)),””,INDIRECT(TRIM($B2)&”!U2″))