• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About

INDIRECT Function in Excel

The INDIRECT function allows you to use a cell reference entered as a text string. This can be used to:

  • Ensure the reference does not change when additional rows and columns are inserted in the worksheet
  • Create a reference from letters and numbers entered into other cells. This also enables you to change the reference in the formula without editing the formula itself
  • Refer to a named range. Very useful when used with Data Validation to create dependent drop down lists

The syntax for the INDIRECT function is:

=INDIRECT(ref_text, [a1])

ArgumentPurpose
ref_textThe referenced range. Can be entered as a cell reference, text string that creates the cell reference or a range name
a1The type of reference used in ref_text. True, or omitting the argument, uses the A1 style. False uses the R1C1 style

The example below demonstrates the INDIRECT function being used to create a cell range using a text string. Using this method allows us to alter the function by changing cells E1 and E2 as opposed to changing the function itself. It also locks the cells so the function will not change when new rows are added.

Excel INDIRECT function used with cell references specifying a range

The example below demonstrates the INDIRECT function being used to return the total for the South region. The value in cell E1 refers to a named range and the INDIRECT function allows us to pass this to the SUM function

INDIRECT function with named ranges

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • Conditional Formatting Multiple Columns – 3 Examples
  • IF Function in Power Query Including Nested IFS

Recent Posts

  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
  • Display Negative Time in Excel
  • Lookup Multiple Values in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·