Example 1: Used for Indirect Referencing
We have two tables and we want to have the values of the sales of the first table in the second table in the Sales column. Paste these values with indirect address referencing by using the INDIRECT function.
➤Select the output cell F5.
➤Type the following formula
=INDIRECT("C"&ROW(C5))- ROW(C5)→ returns the row number of cell C5
Output→ 5
- INDIRECT(“C”&ROW(C5)) becomes
INDIRECT(“C5”)→ returns the value in cell C5
Output→ $4,629.00
➤Press ENTER.
➤Drag down the Fill Handle Tool.
Result:
Get the values of the sales in the Sales column of the second table by using indirect reference.
Example 2: Adding up Values with the Excel INDIRECT Function
Sum up the sales values by using indirect referencing.
➤Select the output cell D9.
➤Type the following formula
=INDIRECT("D5")+INDIRECT("D6")+INDIRECT("D7")+INDIRECT("D8")- INDIRECT(“D5”)→ returns the value in the cell D5
Output→ $4,629.00
- INDIRECT(“D6”)→ returns the value in the cell D6
Output→ $3,257.00
- INDIRECT(“D7”)→ returns the value in the cell D7
Output→ $2,091.00
- INDIRECT(“D8”)→ returns the value in the cell D8
Output→ $2,125.00
- INDIRECT(“D5”)+INDIRECT(“D6”)+INDIRECT(“D7”)+INDIRECT(“D8”)→ becomes
$4,629.00+$3,257.00+$2,091.00+$2,125.00
Output→ $12,102.00
➤Press ENTER.
Result:
Get the sum of sales in the D9 cell.
Method 1 – Combining Excel INDIRECT ADDRESS Functions to Copy Values From Another Sheet
We have three different sheets named January, February, and March and each of them contains the sales of the products.
Paste the sales values from these sheets in the following table in the corresponding column of these months by using indirect address reference.
➤Select the output cell C5.
➤Type the following formula
=INDIRECT("January!"&ADDRESS(ROW(D5),COLUMN(D5)))- ROW(D5)→returns the row number of the cell D5
Output→ 5
- COLUMN(D5)→returns the column number of the cell D5
Output→ 4
- ADDRESS(ROW(D5),COLUMN(D5)) becomes
ADDRESS(5,4)
Output→$D$5
- INDIRECT(“January!”&ADDRESS(ROW(D5),COLUMN(D5))) becomes
INDIRECT(“January!”&”$D$5”)→INDIRECT(“January!$D$5”)
Output→$4,629.00
➤Press ENTER.
➤Drag down the Fill Handle Tool.
Get the sales record of January month from the January sheet in the January column.
Get the sales record for February and March by using the following formulas
=INDIRECT("February!"& ADDRESS(ROW(D5),COLUMN(D5)))
=INDIRECT("March!"& ADDRESS(ROW(D5),COLUMN(D5)))
Method 2 – Using the INDIRECT Function and ADDRESS Function of Excel for Indirect Referencing
We want to have the values of the sales of the first table in the second table in the Sales column. Paste these values with indirect address referencing by using the INDIRECT function and the ADDRESS function. Use the row numbers of Row No. column.
➤Select the output cell G5.
➤Type the following formula
=INDIRECT(ADDRESS(D5,3))- D5→ returns the value in the cell D5
Output→ 5
- ADDRESS(D5,3) becomes
ADDRESS(5,3))→ returns the cell address
Output→ $C$5
- INDIRECT(ADDRESS(D5,3)) becomes
INDIRECT(“$C$5”)
Output→ $4,629.00
➤Press ENTER.
➤Drag down the Fill Handle Tool.
Result:
Get the values of the sales in the Sales column of the second table by using indirect reference.
Download Practice Workbook
Further Readings
- How to Use Excel INDIRECT Range
- INDIRECT Function with Sheet Name in Excel
- Create Drop-Down List Using INDIRECT Function in Excel
- How to Convert Text to Formula Using the INDIRECT Function in Excel
<< Go Back to Excel INDIRECT Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
