MrExcel.com - Your One Stop for Excel Tips & Solutions

Refer To A Cell Whose Address Varies, Based On A Calculation

February 08, 2021 - by

Refer To A Cell Whose Address Varies, Based On A Calculation

Challenge: You need to refer to a cell, but the cell address varies, based on a calculation.

Solution: The INDIRECT function takes an argument that looks like a cell reference and returns the value in that reference.

In Figure 22, the formula in row 2 asks for the INDIRECT of the cell immediately above the formula. So, in D2, the formula tells Excel to get the INDIRECT of cell D1. Cell D1 has a valid cell address of C9. The formula returns the current value from cell C9, which is 17.

Figure 22. You can use the INDIRECT function to specify a cell address, and Excel returns the value at that address.
Figure 22. You can use the INDIRECT function to specify a cell address, and Excel returns the value at that address.

Additional Details: In Lotus 1-2-3, this function was the @@ (“at-at”) function.

Additional Details: The argument for INDIRECT can be a named range. You could create an ad hoc reporting engine by using named ranges and INDIRECT.

In Figure 23, a named range has been set up for each column in a report.

Figure 23. You can set up a named range for each column.
Figure 23. You can set up a named range for each column.

Tip: See page 76 for a method to quickly create many range names.

Cell C12 in Figure 24 contains a validation dropdown list that allows a person to choose any of the headings in A1:F1 (Figure 24).

Figure 24. You can add a validation dropdown to allow someone to select a heading.
Figure 24. You can add a validation dropdown to allow someone to select a heading.

The formula in B13 uses concatenation to build a proper label for the cell. The formula could be:

="Total " & C12

or

=CONCATENATE("Total ", C12)

The formula in C13 asks for the SUM of the INDIRECT of the name in C12. In Figure 25, C12 contains the word COGS. Because COGS is defined as C2: C10, Excel sums the range C2:C10 and returns the answer as the result of the formula.

Figure 25. The INDIRECT formula returns all the values in a named range.
Figure 25. The INDIRECT formula returns all the values in a named range.

When someone chooses a new metric from cell C12, the INDIRECT formula sums a different column. Figure 26 shows the total expenses.

Figure 26. When you change the metric in C12, the formula totals a different column. The total in the status bar in the lower right verifies that the formula is working.
Figure 26. When you change the metric in C12, the formula totals a different column. The total in the status bar in the lower right verifies that the formula is working.

Additional Details: The argument for INDIRECT can be calculated on the fly. In Figure 27, cell C3 concatenates a column letter from C1 and a row number from C2, and it returns a value from B8.

Figure 27. Here, the reference is concatenated from values in other cells.
Figure 27. Here, the reference is concatenated from values in other cells.

Summary: The INDIRECT function allows you to calculate from where to pull a value.

Title Photo: Luis Alfonso Orellana at Unsplash.com

This article is an excerpt from Excel Gurus Gone Wild.