Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Extract data with helper column in Excel
  • Basic INDEX MATCH approximate in Excel
  • How to get first column number in range in Excel
  • Get nth match with INDEX / MATCH in Excel
  • How to get relative column numbers in a range in Excel

Data Analysis Examples

  • How to calculate current stock or inventory in Excel
  • Conditional Formatting Color Scales Examples in Excel
  • How to create dynamic reference table name in Excel
  • Move chart to a separate worksheet in Excel
  • Understanding Anova in Excel

Data Validation Examples

  • Excel Data validation exists in list
  • Excel Data validation with conditional list
  • Excel Data validation whole percentage only
  • Excel Data validation allow weekday only
  • Excel Data validation number multiple 100

Convert text string to valid reference in Excel using Indirect function

by

Applying Excel  INDIRECT function to Cell Reference, Range Reference, Named Range, Worksheet Reference.

Use the INDIRECT function in Excel to convert a text string into a valid reference. You can use the & operator to create text strings.

Cell Reference

Use the INDIRECT function in Excel to convert a text string into a valid cell reference.

1. For example, take a look at the INDIRECT function below.

indirect function Convert text string to valid reference in Excel using Indirect function

Explanation: =INDIRECT(A1) reduces to =INDIRECT(“D1”). The INDIRECT function converts the text string “D1” into a valid cell reference. In other words, =INDIRECT(“D1”) reduces to =D1.

2. The simple INDIRECT function below produces the exact same result.

Worked Example:   How to get address of first cell in range in Excel

simple indirect function Convert text string to valid reference in Excel using Indirect function

3. Do we really need the INDIRECT function? Yes. Without using the INDIRECT function, this would be the result.

without indirect function Convert text string to valid reference in Excel using Indirect function

4. Use the & operator to concatenate “D” with the value in cell A1.

cell referencing Convert text string to valid reference in Excel using Indirect function

Explanation: the formula above reduces to =INDIRECT(“D1”). Again, =INDIRECT(“D1”) reduces to =D1.

Range Reference

Use the INDIRECT function in Excel to convert a text string into a valid range reference. For example, combine the SUM function and the INDIRECT function.

sum indirect function Convert text string to valid reference in Excel using Indirect function

Explanation: the formula above reduces to =SUM(INDIRECT(“D3:D6”)). The INDIRECT function converts the text string “D3:D6” into a valid range reference. In other words, =SUM(INDIRECT(“D3:D6”)) reduces to =SUM(D3:D6).

Worked Example:   Dynamic Named Range in Excel

Named Range

Use the INDIRECT function in Excel to convert a text string into a valid named range.

1. For example, the AVERAGE function below uses the named range Scores.

simple named ranged Convert text string to valid reference in Excel using Indirect function

Explanation: the named range Scores refers to the range D1:D3.

2. However, the AVERAGE function below returns an error.

div error Convert text string to valid reference in Excel using Indirect function

Explanation: =AVERAGE(“Scores”) returns an error because Excel cannot calculate the average of a text string!

3. The INDIRECT function below does the trick.

average indirect function Convert text string to valid reference in Excel using Indirect function

Explanation: =AVERAGE(INDIRECT(“Scores”)) reduces to =AVERAGE(Scores).

Worksheet Reference

Use the INDIRECT function in Excel to create a dynamic worksheet reference.

1. This is what a simple worksheet reference looks like.

worksheet reference Convert text string to valid reference in Excel using Indirect function

Note: cell A1 on Sheet1 contains the value 10. Cell A1 on Sheet2 contains the value 20. Cell A1 on Sheet3 contains the value 30.

Worked Example:   How to strip non-numeric characters in Excel

2. On the Summary sheet, enter the INDIRECT function shown below. Use the & operator to concatenate the sheet name in cell A1 with “!A1”.

dynamic worksheet reference Convert text string to valid reference in Excel using Indirect function

Explanation: the formula above reduces to =INDIRECT(“Sheet1!A1”). The INDIRECT function converts the text string “Sheet1!A1” into a valid worksheet reference. In other words, =INDIRECT(“Sheet1!A1”) reduces to =Sheet1!A1.

3. If your sheet names contain spaces or other special characters, enclose the sheet name in single quotation marks. Modify the INDIRECT function as shown below.
add-single-quotation-marks Convert text string to valid reference in Excel using Indirect function

Post navigation

Previous Post:

Two-column Lookup in Excel

Next Post:

Excel MAX, MATCH and ADDRESS function illustration

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Remove last characters from right in a cell in Excel
  • How to compare two text strings in Excel
  • How to extract text between parentheses in Excel
  • How to count number of characters of text in a cell in Excel
  • Convert text to numbers using VALUE function in Excel
  • Convert Unix time stamp to Excel date
  • WEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • Series of dates by day
  • Calculate date overlap in days in Excel
  • Count birthdays by month in Excel
  • FV function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate principal for given period in Excel
  • XIRR function: Description, Usage, Syntax, Examples and Explanation
  • ISPMT function: Description, Usage, Syntax, Examples and Explanation
  • YIELD function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles