Indirect Formula for an analyst

[amartino44]

Hello,
Day three of my quest to become excel savvy Today, I would like to focus on the Indirect formula. Could someone please let me know cool ways and examples to use this formula (sites and/or examples are welcome) Thank you!!!!

[FDibbins]

a few samples...

- try using indirect() to reference a cell on different sheets (sheet2, sheet3 etc) from a list of sheet names in sheet1

- create a few similar range names (range1, range2 etc), and use a list to refence them

[Jacc]

You can for example use INDIRECT to create dynamic charts:
http://www.excelforum.com/excel-prog...ic-charts.html

[daddylonglegs]

In simple terms INDIRECT is used to convert text to a valid cell reference, e.g. if cell G2 contains the text D3 then this formula

=INDIRECT(G2)

will return the contents of D3

....but beware! INDIRECT function is one of several Excel functions that is volatile (OFFSET is another). Volatile means that formulas containing such functions are re-calculated every time something changes in the worksheet (even if it doesn't directly affect that cell). If you use many formulas with volatile functions that may slow your workbook, see here for more.

Having said that there are some very useful attributes of INDIRECT. You can use it to refer to a specific cell in another worksheet, where that worksheet name is contained in a cell, e.g. if A1 contains the text "Data" (without quotes) then this formula refers to cell B2 on Data worksheet

=INDIRECT("'"&A1&"'!B2")

Change the sheet name in A1 and the formula will now refer to a different worksheet.

Only INDIRECT function can do that.

I also use it to generate arrays of numbers, e.g. if you want to generate an array of all integers from 1 to 100 for use in a formula then you can use ROW, i.e.

=ROW(1:100)

but because that will change if you insert rows in the worksheet you can "protect" with INDIRECT, i.e.

=ROW(INDIRECT("1:100"))

now that doesn't change even if you insert rows or delete rows in the worksheet (because "1:100" is a text value that doesn't change like a cell reference would)

To move on one more step.....what if I wanted to generate integers 1 to n where n is a value contained in a cell, e.g. Z2? I can use the same formula but now containing that cell reference, i.e.

=ROW(INDIRECT("1:"&Z2))

If Z2 contains 9 that generates this array

{1;2;3;4;5;6;7;8;9}

[twiggywales]

it may also be worth noting the Syntax of INDIRECT

=INDIRECT(ref_text,a1)

obvioulsy the text part is simple, you can use your references in the usual way
eg

Please Login or Register  to view this content.

will create a reference for J5 (J from text and 5 from ROW(A5))

the a1 part of the Syntax part is the fun part this is set to true or false (true default) if you set this to false it alters your reference system from typical A1 style to as R1C1 style (row1 column1) = A1 (first row first column)

so with this would could also reference J5 with this formula

Please Login or Register  to view this content.

but why is that useful

well for starters functions like MATCH return the row or column number and can be used in your references without using INDEX or other functions to lookup the result which can be useful.

to take it a step further you can use ADDRESS in the INDIRECT function and go back to not needing the RC Style reference.

please see my attached book it shows several ways to use

INDIRECT.xlsx

[amartino44]

Thanks all. Very useful stuff