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

Data Analysis

  • Everything about Charts in Excel
  • How to combine 2 or more chart types in a single chart in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • How to calculate correlation coefficient Correlation in Excel
  • How to create dynamic reference table name in Excel

References

  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • Get nth match with INDEX / MATCH in Excel
  • How to use Excel VLOOKUP Function
  • To count total rows in a range in Excel
  • How to reference named range different sheet in Excel

Data Validations

  • Excel Data validation allow weekday only
  • Excel Data validation specific characters only
  • Excel Data validation date in specific year
  • Excel Data validation whole percentage only
  • Excel Data validation require unique number

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:   Check multiple cells are equal 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:   Count day of week between dates 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 count total columns in range 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

Logical Functions

  • OR function Examples in Excel
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • Check multiple cells are equal in Excel
  • AND function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Calculate days remaining in Excel
  • How to get Weekdays, Working days between Two Dates in Excel
  • Calculate series of dates by workdays in Excel
  • Get first Monday before any date in Excel
  • Convert date to text in Excel

Grouping

  • Group numbers with VLOOKUP in Excel
  • Running count group by n size in Excel
  • Group times into unequal buckets in Excel
  • Group times into 3 hour buckets in Excel
  • Categorize text with keywords in Excel

General

  • Excel Autofill Cell Ranges, Copy, Paste
  • Subtotal by invoice number in Excel
  • How to calculate percent of goal in Excel
  • How to calculate percent sold in Excel
  • Spell Check in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning