Indirect Function in Excel

Indirect Function in Excel

‍

‍

‍

How Well Do You Know Indirect Function?

In this blog, we will talk about a unique formula in Microsoft Excel called Indirect. The indirect function is where you can dynamically reference a cell from different worksheets on a single worksheet. In order to understand this formula better, keySkillset will take a closer look at the example below.

As you can see here, we have two different sheets with the same format named as Tom and John. In both of these spreadsheets, we have the sales numbers written in the cellB2. To use indirect formula, it is essential to have the referenced value in the same cells in different sheets. For using the indirect function, we need to specify the name of the sheets correctly. Since we defined as Tom and John, the reference value inside the indirect function will lookup the value inside the selected cell amongst the sheets in the workbook and will gather a unique cell value from that specific sheet. After we set out the same formatted worksheets, we can continue with how the indirect function works.

How Indirect Function Works?

For indirect function, we need two different parameters to enter. These are the reference and the cell location that we want to gather from a specific sheet. First, we need to define the value that we will search through the sheet names to move forward and return a specific value. Currently, we have our sheets’ names defined as the names of the people.  In addition, we have the names in the cell B1 in the sheet where we will use the indirect function. Therefore, we defined our first parameter as cell B1.

Since we want to gather the sales values from the selected sheets, we need to type “!B2”. It will gather the value in cell location B2 in the selected sheet. After we defined our parameter, we enter okay. The formula will look the cell B1, search the sheet named as the value inside B1 and return the value in the cell B2 from that sheet. Since we have John in cell B1, the formula will go to the sheet John and return the sales value from that sheet. If we change the name and type Tom, the formula will return the sales value from the sheet named Tom.

‍

‍

‍

Indirect is one of the unique formulas that can be extremely useful in specific situations. Especially, if you have automated reports generated with macros or different advanced programs with the same format for every sheet. By using the indirect formula you will save a lot of time when dealing with the dynamic referencing system.

‍

Checkout how simulation based learning can improve your Speed and help you have more hours in your hand.

‍

Check out - Excel Efficiency at keySkillset

‍