## INDIRECT function

**INDIRECT**(*ref_text*,*a1*)

### Arguments

*Ref_text*is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string.*a1*specifies the type of reference in argument*ref_text*. True or omitted ref_text is a A1-style reference. False signals that ref_text is a R1C1-style reference.

### Explaining Indirect function

This is what Microsoft tells you about the indirect function:

*Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.*

Let me try to explain that, cell A6 contains this formula:=INDIRECT(B6). If you enter a cell reference like B1 in cell B6, the formula in cell A6 becomes =B1 and it returns the value in cell B1. This animated picture explains it all.

Can it do more than that? The answer is of course yes but first a small warning, INDIRECT is a volatile function and extended use may slower your workbook considerably.

### Example 1 - Avoid automatically changing cell references

When you insert or delete a row or column in excel the cell references in formulas changes, even if you use absolute cell references. The Indirect function helps you solve that problem. The following picture demonstrates what happens with relative, absolute and indirect cell references when you insert a row.

### Example 2 - Sum a range you specify

Formula in cell E3:

Cell A1 contains C3:C5. Sum function sums values in cell C3:C5. If you change the value in cell F3 to C3:C6 it sums the values in cell range C3:C6.

### Example 3 - Dynamic cell ranges

A drop down list lets you select a quarter. The formula in cell G3 sums the corresponding values.

Formula in cell G3:

If you don´t know what to do during the holidays, make sure you check out these functions and custom functions.

### Functions I have written about

- Mod function
- Hyperlink example
- Randbetween and Rand functions
- IFERROR function
- Frequency function
- Sumproduct function
- Search and Find functions
- Mmult function
- Small and Large function
- Row function
- Countifs function
- If function
- Countif function
- Match function
- Index function
- Offset function

### User defined functions

You have also the possibility to build your own functions, they are *user defined functions*. They are made in the vb editor using vba. Here are a some custom functions I have built.

- Excel udf: Lookup and return multiple values concatenated into one cell
- Count the number of cells within a range that match multiple comma separated values
- Extract unique distinct values from a filtered table (udf and array formula)
- Excel udf: Reorganize data
- Excel udf: Looking up data in multiple cross reference tables
- Excel udf: Sumif across multiple sheets
- Excel udf: Import historical stock prices from yahoo – added features
- Excel recursive udf: List files in a folder and subfolders
- Excel udf: Filter unique distinct records (case sensitive)
- Excel udf: Filter unique distinct values (case sensitive)
- Excel udf: Combine cell ranges into a single range while eliminating blanks
- Excel udf: Word frequency
- Excel udf: Fuzzy lookups
- Excel udf: Find positive and negative amounts that net to zero
- Excel udf: Find numbers in sum
- Excel udf: List permutations without repetition
- Excel udf: Filter values existing only in one out of two ranges
- Excel udf: Filter common values between two cell ranges in excel
- Filter unique words from a range in excel (udf)
- Filter unique distinct words from a cell range in excel (udf)
- Filter duplicate words from a cell range in excel (udf)

### Download excel *.xlsx file

### Leave a Reply

**How to add vba code to your comment:**

[vb 1="vbnet" language=","]

your code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**How to upload a file**

Upload file

Hi Oscar,

To avoid array formula,and use Text to columns, can be used:

Sub Splitcell()

[A1].TextToColumns [A1].Offset(, 2), xlDelimited, , , 0, 0, 1, 0, 0

End sub

PS

Your site is excellent. One of the best of the best.

Ingolf

Ingolf,

thank you!

Two points about your SplitValues UDF...

First, when the Split function will be called only once during a procedure, there is no need to assign the output from the Split function to an intermediary dynamic array in order to work with its output (doing so similar to selecting a range and then woring with the Selection object instead of the range itself); rather, you can work directly with the Spiit function itself...

Second, the way you have implemented your function, you need to know, in advance, how many substrings the 'b' argument will split into so that you can select exactly that amount of cells to array-enter your formula in, otherwise, any excess selected cells will display the #N/A error which, of course, looks kind of ugly but would be necessary if the text passed into the 'b' argument could vary in the number of delimiters over time. We can eliminate the #N/A errors for any excess selected cells fairly easily using the Application.Caller object like so...

Rick Rothstein (MVP - Excel),

Interesting comment, as always.

Your second point was new to me.

[quote]

Second, the way you have implemented your function, you need to know, in advance, how many substrings the 'b' argument will split into so that you can select exactly that amount of cells to array-enter your formula in, otherwise, any excess selected cells will display the #N/A error which, of course, looks kind of ugly but would be necessary if the text passed into the 'b' argument could vary in the number of delimiters over time.

[/quote]

Not too sure..

Sub SplitCell()

With Range("A1", Cells(Rows.Count, "A").End(xlUp))

.TextToColumns .Offset(, 2), xlDelimited, , , 0, 0, 1, 0, 0

End With

End Sub

I was referring to problems associated with implementing the UDF in my previous posting... macros (like what you posted) are completely different than UDFs and do not have the same (or virtually any such) restrictions. As for determining the vertical extent of the cells to apply the Text-To-Columns method to... that is not necessary as Text-To-Columns will only work on cells with data, so applying it to the entire column will work the same as restricting it to your calculated range...

Columns("A").TextToColumns .Offset(, 2), xlDelimited, , , 0, 0, 1, 0, 0