Easy Guide to Excel’s INDIRECT Function #1

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:

AB
Product NameQuantity
P190
P285

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:

AB
NameScore
Alice90
Bob85
Carol95

In cell D2, enter the formula =INDIRECT(“B” & C1).

Here’s what the setup looks like:

ABCD
NameScoreRowScore
Alice902=INDIRECT(“B” & C2)
Bob85
Carol95
  • 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:

AB
MetricValue
Revenue10000

February Sheet:

AB
MetricValue
Revenue12000

March Sheet:

AB
MetricValue
Revenue15000

ii). Create the Summary Sheet:

In a new sheet named Summary, set up the following data:

AB
MonthRevenue
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:

AB
CampaignNames
CampaignA
CampaignB
CampaignC
CampaignD
CampaignE
CampaignF
CampaignG
CampaignH
CampaignI
CampaignJ

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:

AB
CampaignCampaign 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:

AB
RegionRevenue
North$500,000

SouthRegion:

AB
RegionRevenue
South$700,000

EastRegion:

AB
RegionRevenue
East$600,000

WestRegion:

AB
RegionRevenue
West$800,000

ii). Create the Summary Sheet:

In a new sheet named Summary, set up the following data:

AB
RegionRevenue
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:

AB
Sales$5000
Expenses$3000
ROI60%

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

  1. Display Total Sales for February month.
  2. 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.

Supercharge Your Marketing Strategy with Excel’s SUMPRODUCT #1
Understanding the SWITCH Function in Excel

ExcelMicrosoft

Recent Blogs

Unleash the Power of Dropdown Menus: Elevate Your Website Design with 2 examples

A useful and space-saving way for visitors to access a variety of options, dropdown menus are an essential component of web design. This thorough tutorial will cover the principles of dropdown menus, their significance in web design, and how to improve their usability and appearance using CSS pseudo-elements::before and ::after. Understanding Dropdown Menus Dropdown menus, […]

CSSHTMLUi Design

Optimizing Web Design with CSS Variables: Your Ultimate Guide #2

Understanding CSS Variables Understanding CSS variables requires looking into the concept of cascading style sheets (CSS) and how variables provide a more efficient and flexible method of creating web pages. CSS variables, often known as custom properties, enable developers to build reusable CSS values that may be applied throughout a stylesheet. This not only improves […]

CSSHTMLJavascriptWeb design

Mastering JavaScript Fundamentals: Syntax, Variables, and Comments #2

Introduction to JavaScript JavaScript is a programming language that is widely used in web development. It enables the dynamic and interactive components we see on practically every website, ranging from simple animations to large web apps. Understanding JavaScript is not only useful, but also necessary if you want to work in web development. JavaScript is […]

CSSHTMLJavascript

Understanding CSS Functions: A Comprehensive Guide #1

CSS Functions Introduction Cascading Style Sheets (CSS) are used extensively in web development to create and style online pages. CSS properties are most recognised for defining styles like as colours, fonts, and layouts, but they also add flexibility and dynamism to stylesheets.In this comprehensive guide, we’ll look at various CSS functions, syntax, and global applications. […]

CSSHTMLWeb design