Refer To A Cell Whose Address Varies, Based On A Calculation
February 08, 2021 - by Bill Jelen

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.

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.

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).

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.

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

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.

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.