Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to Sort by Color in Excel
  • Conditional Formatting New Rule with Formulas in Excel
  • How to create Gauge Chart in Excel
  • Error Bars in Excel
  • Add Outline to Data in Excel

References

  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • How to retrieve first match between two ranges in Excel
  • How to get first column number in range in Excel
  • How to get last row in text data in Excel
  • How to use Excel MATCH Function

Data Validations

  • Excel Data validation no punctuation
  • Excel Data validation date in next 30 days
  • Excel Data validation allow uppercase only
  • Excel Data validation require unique number
  • Excel Data validation specific characters only

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.

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

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.

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:

VLOOKUP without #N/A error in Excel

Next Post:

How to Create Gantt Chart in Excel

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

Logical Functions

  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Logical Functions, References and Examples
  • SWITCH function example in Excel
  • Not Equal To ‘<>‘ operator in Excel

Date Time

  • Get last weekday in month in Excel
  • MONTH function: Description, Usage, Syntax, Examples and Explanation
  • Get date from day number in Excel
  • How to get Holiday Date from Year in Excel
  • Count times in a specific range in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • Calculate conditional mode with criteria in Excel
  • Group times into 3 hour buckets in Excel
  • Categorize text with keywords in Excel
  • Group arbitrary text values in Excel

General

  • How to choose page/paper size in Excel before Printing
  • Share Excel data with Word documents
  • Subtotal invoices by age in Excel
  • Excel Ribbon Quick Overview For Beginners
  • Convert column number to letter in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning