Table of Contents
How to Use Excel’s INDIRECT Function
In the Excel, the INDIRECT function is a versatile tool that can considerably improve your spreadsheet capabilities. Whether you’re a data analyst, financial planner, or casual user, knowing how to use the INDIRECT function will help you optimise workflows and offer dynamic flexibility to your data management. This blog will go into the details of the INDIRECT function, including syntax, practical applications, and recommendations for maximising its capabilities.
What is the INDIRECT Function?
Excel’s INDIRECT function returns the reference given by a text string. This implies that you can dynamically refer to other cells or ranges in your spreadsheet by changing the text string, rather than manually changing each reference. This functionality is very beneficial for developing flexible formulas that can adjust to changes without requiring frequent updates.
Syntax:
The syntax for the INDIRECT function is straightforward:
INDIRECT(ref_text, [a1])
- ref_text: This text string represents the reference. It may be a cell address, a range, or even a named range.
- [a1]: This is an optional parameter. If TRUE or omitted, ref_text is interpreted as an A1-style reference (such as “A1”). If FALSE, ref_text is handled as an R1C1-style reference (such as “R1C1”).
let’s see simple example:
| A | B |
| Product Name | Quantity |
| P1 | 90 |
| P2 | 85 |
You want to use the INDIRECT function to reference the Quantity of P1 dynamically.
Using INDIRECT:
Instead of directly referring to B2, you can construct the reference using the INDIRECT function.
=INDIRECT(“B2”)
This formula will return 90, which is Alice’s score. Here’s how it works:
- The string “B2” is passed to the INDIRECT function.
- INDIRECT(“B2”) converts this string into a reference to cell B2.
- Excel then returns the value in cell B2, which is 90.
INDIRECT can be a really handy function. Let’s look at a few instances to better grasp the INDIRECT function.
You have a table of student scores in columns A and B:
| A | B |
| Name | Score |
| Alice | 90 |
| Bob | 85 |
| Carol | 95 |
In cell D2, enter the formula =INDIRECT(“B” & C1).
Here’s what the setup looks like:
| A | B | C | D |
| Name | Score | Row | Score |
| Alice | 90 | 2 | =INDIRECT(“B” & C2) |
| Bob | 85 | ||
| Carol | 95 |
- C2 contains 2, indicating you want to reference the score in row 2.
- The formula =”B” & C2 concatenates the string “B” with the value in C2, resulting in the string “B2”.
- INDIRECT(“B2”) converts this string into a cell reference, which points to cell B2.
- Cell B2 contains the value 90.
- So, D2 will now display 90.
If you change C2 to 3:
So, D1 will now display 85.
Practical Applications of the INDIRECT Function
1. Dynamic Cell References
You have several pages in your Excel worksheet, each reflecting data from a different month. You wish to dynamically refer to a certain cell from these sheets based on the month indicated in a cell on your summary spreadsheet.
i). Set Up the Data in Different Sheets:
Create three sheets: January, February, and March. In each sheet, insert the following data in cells A1 and B2:
January Sheet:
| A | B |
| Metric | Value |
| Revenue | 10000 |
February Sheet:
| A | B |
| Metric | Value |
| Revenue | 12000 |
March Sheet:
| A | B |
| Metric | Value |
| Revenue | 15000 |
ii). Create the Summary Sheet:
In a new sheet named Summary, set up the following data:
| A | B |
| Month | Revenue |
| January | =INDIRECT(“‘” & A2 & “‘!B2”) |
Note:
- When using the INDIRECT function to reference cells in other sheets, make sure that the sheet name supplied in the formula corresponds to the actual sheet name in the workbook. This is critical for Excel to accurately read the reference.
- Cell A2 of the summary sheet (Summary in our example) can dynamically decide which sheet’s data is being referenced via INDIRECT. So, if A2 contains January, the algorithm will extract data from that sheet.
Building the Formula =INDIRECT(“‘” & A2 & “‘!B2”) :
Given a sheet name in cell A1 and a desire to reference cell B2 in that sheet, here’s how the formula works:
- Concatenation: The formula =INDIRECT(“‘” & A2 & “‘!B2”) joins elements to create a complete reference.
- “‘”: This adds the starting single quote.
Single quotes are used around the sheet name if it contains spaces or special characters. While sheet names without spaces are not required, they are recommended to eliminate potential errors.
For example, if your sheet name is January Sales, the reference must be ‘January Sales’!B2. The single quotes ensure Excel treats January Sales as a single sheet name.
⦁ A2: This takes the sheet name from cell A2.
⦁ “‘!B2”: This adds the ending single quote, the exclamation mark, and the cell reference B2.
Exclamation Mark (!):
- The exclamation mark separates the sheet name from the cell reference within that sheet.
- For example, January!B2 tells Excel to look at cell B2 in the sheet named January.
Explanation of the INDIRECT Formula:
- A2 contains the month name January.
- The formula =”‘” & A2 & “‘!B2” concatenates the string “‘ with the value in A2, then with the string “‘!B2”. This results in the string ‘January’!B2.
- INDIRECT(“‘” & A2 & “‘!B2”) creates a cell reference for cell B2 on the January sheet.
- Cell B2 on the January sheet contains the value 10000.
Therefore, cell B2 on the Summary sheet will display 10000.
Making It Dynamic:
If you change the value in cell A2 to February, the formula will dynamically update:
- A2 now contains February.
- The formula =”‘” & A2 & “‘!B2” becomes “‘February’!B2”.
- INDIRECT(“‘” & A2 & “‘!B2”) references cell B2 on the February sheet, which contains 12000.
- So, B2 on the Summary sheet will now display 12000.
2. Creating Flexible Named Ranges
Your Excel workbook contains numerous sheets, each of which represents data for a distinct marketing campaign. You wish to dynamically refer to a specific range of cells (columns) containing campaign names from these sheets based on the campaign indicated in a cell on your summary sheet.
i). Set Up the Data in Different Sheets:
Create three sheets named Campaign1, Campaign2, and Campaign3. In each sheet, enter the following campaign names in cells A1:A10:
Campaign1 Sheet:
| A | B |
| Campaign | Names |
| Campaign | A |
| Campaign | B |
| Campaign | C |
| Campaign | D |
| Campaign | E |
| Campaign | F |
| Campaign | G |
| Campaign | H |
| Campaign | I |
| Campaign | J |
Campaign2 Sheet: Same as above, but with different campaign names
Campaign3 Sheet: Same as above, but with different campaign names
ii). Create the Summary Sheet:
In a new sheet named Summary, set up the following data:
| A | B |
| Campaign | Campaign Names |
| Campaign1 | =INDIRECT(“‘” & A2 & “‘!$A$1:$A$10”) |
Building the Formula INDIRECT(“‘” & A2 & “‘!$A$1:$A$10”):
- A2 contains the name of the sheet (Campaign1).
- The formula constructs the string ‘Campaign1′!$A$1:$A$10.
- INDIRECT interprets this string as a reference to the range $A$1:$A$10 (column A) in the Campaign1 sheet.
- $ Symbol (Absolute Reference):
Using the $ symbol in a cell reference creates an absolute reference. This means that if you copy or fill the formula into another cell, the referred row or column will not change.
The $ sign makes both the column letter (A) and the row number (1 to 10) absolute. This means that when you copy a formula with an absolute reference, the reference remains fixed and does not change when copied to other cells. - Relative Reference (A1:A10):
When you copy a formula with a relative reference (without the $ symbols) to another cell, the reference changes to reflect the new location. For example, if you move the formula one cell to the right, A1:A10 becomes B1:B10. - The campaign names are displayed in column B of the Summary sheet after retrieving them from the specified range.
How INDIRECT Works:
- A2 contains the name of the sheet (Campaign1).
- The formula constructs the string ‘Campaign1’!$A$1:$A$10.
- INDIRECT interprets this string as a reference to the range $A$1:$A$10 (column A) in the Campaign1 sheet.
- Campaign names are displayed in column B of the Summary sheet, retrieved from the specified range.
Making It Dynamic:
If you change A2 in the Summary sheet to Campaign2:
The formula =INDIRECT(“‘” & A2 & “‘!$A$1:$A$10”) will now reference the range of campaign names in the Campaign2 sheet.
3. Consolidating Data from Multiple Sheets
Suppose we have the following sheets in our Excel workbook:
i). Set Up the Data in Different Sheets:
NorthRegion:
| A | B |
| Region | Revenue |
| North | $500,000 |
SouthRegion:
| A | B |
| Region | Revenue |
| South | $700,000 |
EastRegion:
| A | B |
| Region | Revenue |
| East | $600,000 |
WestRegion:
| A | B |
| Region | Revenue |
| West | $800,000 |
ii). Create the Summary Sheet:
In a new sheet named Summary, set up the following data:
| A | B |
| Region | Revenue |
| East | East =SUM(INDIRECT(“‘NorthRegion’!B2”), INDIRECT(“‘SouthRegion’!B2”), INDIRECT(“‘EastRegion’!B2”), INDIRECT(“‘WestRegion’!B2”)) |
- INDIRECT(“‘RegionName’!B2”): This part of the formula dynamically constructs a reference to cell B2 in each region’s sheet.
For example, INDIRECT(“‘NorthRegion’!B2”) references cell B2 in the NorthRegion sheet. - SUM(…): This function sums the values obtained from the INDIRECT functions.
When you enter the formula =SUM(INDIRECT(“‘NorthRegion’!B2”), INDIRECT(“‘SouthRegion’!B2”), INDIRECT(“‘EastRegion’!B2”), INDIRECT(“‘WestRegion’!B2”)), Excel will calculate the sum of the revenue data from cell B2 in each region’s sheet:
=SUM(500000, 700000, 600000, 800000) = 2600000
So, the result of the formula will be $2,600,000, which is the total revenue from all regions combined.
4. Combining with Data Validation
Assume you have a marketing data table with many categories such as “Sales”, “Expenses”, “ROI”, and so on. You want to choose one of these categories from a dropdown list using Data Validation and then display the related data in another cell using the INDIRECT function.
i). Set Up the Data in Different Sheets:
| A | B |
| Sales | $5000 |
| Expenses | $3000 |
| ROI | 60% |
ii). Set Up Data Validation:
Set up a dropdown list in cell A1 with the categories “Sales”, “Expenses”, “ROI”. Here’s how you can do it:
- Select cell A1.
- Go to the “Data” tab on the Excel ribbon.
- Click on “Data Validation”.
- In the “Data Validation” dialog box, select “List” from the “Allow” dropdown.
- In the “Source” field, enter Sales,Expenses,ROI (or the range where you have these values).
- Click “OK” to apply the Data Validation.
iii). Display Corresponding Data:
In cell B1, you will use the INDIRECT function to display the data from cell A1 that corresponds to the specified category. Here is the formula:
=INDIRECT(A1)
This formula will utilise the value in cell A1 (the selected category) as a reference to retrieve the value from the appropriate cell in the marketing data table.
When you select a category from the dropdown list in cell A1, the associated data will appear in cell B1.
Test yourself with this example :
Create separate sheets for each month’s sales data. For example, you might have sheets named “January”, “February”, “March”, etc. Each sheet contains a table with sales data for that month, with the same structure (e.g., columns for “Product”, “Quantity”, “Price”, etc.).
- Display Total Sales for February month.
- Calculate total sales using sum(…).
Conclusion
The INDIRECT function in Excel is a powerful tool that may increase the flexibility and functionality of your spreadsheets. Mastering its use allows you to develop more responsive and adaptive data models, expedite difficult operations, and increase the overall productivity of your Excel workflows. INDIRECT is a useful function in Excel for dealing with dynamic references, consolidating data, and generating interactive dashboards.