INDIRECT

 The INDIRECT function in Excel is a powerful tool that allows you to reference cells dynamically. It converts a text string into a cell reference, which can be particularly useful when you want to create flexible formulas or reference cells based on variable inputs. Below are 10 examples of how you can use the INDIRECT function, including both data and formulas.

Example 1: Basic INDIRECT Function

Data:

• Cell A1: 10

• Cell B1: A1

Formula:

excel

Copy

=INDIRECT(B1)

Result: 10
Explanation: The formula in B1 references A1, so INDIRECT(B1) returns the value in A1, which is 10.

Example 2: Combining INDIRECT with ROW and COLUMN

Data:

• Cell A1: 5

• Cell B2: A1

Formula:

excel

Copy

=INDIRECT("A" & ROW(B2))

Result: 5
Explanation: ROW(B2) returns 2, so the formula becomes INDIRECT("A2"), which references A2. However, since B2 contains A1, it indirectly references A1, which contains 5.

Example 3: Using INDIRECT with Sheet Names

Data:

• Sheet1, Cell A1: 15

• Sheet2, Cell A1: Sheet1!A1

Formula:

excel

Copy

=INDIRECT(Sheet2!A1)

Result: 15
Explanation: The formula references Sheet1!A1 through the text string in Sheet2!A1, returning the value 15.

Example 4: Dynamic Range with INDIRECT

Data:

• Cell A1: 1

• Cell A2: 2

• Cell A3: 3

• Cell B1: A1:A3

Formula:

excel

Copy

=SUM(INDIRECT(B1))

Result: 6
Explanation: INDIRECT(B1) converts the text A1:A3 into a range reference, and SUM adds up the values in that range.

Example 5: INDIRECT with Named Ranges

Data:

• Named Range "Sales": A1:A3 (with values 10, 20, 30)

• Cell B1: Sales

Formula:

excel

Copy

=SUM(INDIRECT(B1))

Result: 60
Explanation: INDIRECT(B1) references the named range "Sales", and SUM adds up the values in that range.

Example 6: INDIRECT with Drop-Down List

Data:

• Cell A1: 10

• Cell A2: 20

• Cell A3: 30

• Cell B1: Drop-down list with options A1, A2, A3

Formula:

excel

Copy

=INDIRECT(B1)

Result: Depends on the selection in the drop-down list.
Explanation: If A2 is selected in the drop-down list, the formula returns 20.

Example 7: INDIRECT with R1C1 Reference Style

Data:

• Cell A1: 100

• Cell B1: R1C1

Formula:

excel

Copy

=INDIRECT(B1, FALSE)

Result: 100
Explanation: INDIRECT(B1, FALSE) uses R1C1 reference style, where R1C1 refers to row 1, column 1 (i.e., A1).

Example 8: INDIRECT with Concatenation

Data:

• Cell A1: 50

• Cell B1: A

• Cell C1: 1

Formula:

excel

Copy

=INDIRECT(B1 & C1)

Result: 50
Explanation: B1 & C1 concatenates to A1, so INDIRECT("A1") returns the value in A1.

Example 9: INDIRECT with Multiple Sheets

Data:

• Sheet1, Cell A1: 100

• Sheet2, Cell A1: Sheet1!A1

Formula:

excel

Copy

=INDIRECT("Sheet2!A1")

Result: 100
Explanation: The formula references Sheet2!A1, which contains a reference to Sheet1!A1, so it returns the value 100.

Example 10: INDIRECT with Dynamic Column Reference

Data:

• Cell A1: 10

• Cell B1: 20

• Cell C1: 30

• Cell D1: B

Formula:

excel

Copy

=INDIRECT(D1 & "1")

Result: 20
Explanation: D1 & "1" concatenates to B1, so INDIRECT("B1") returns the value in B1.

Summary

The INDIRECT function is versatile and can be used in various scenarios to create dynamic references in Excel. Whether you're working with sheet names, named ranges, or dynamic cell references, INDIRECT can help you build more flexible and powerful spreadsheets.