Thursday, May 12, 2011

How to Use Excel's Indirect Function


1. Open your worksheet and find the cells that you want to add value to with Indirect functions.
2. Access the 'function text box' for a specific cell. In some Excel programs, the text box is visible at the top of the page. If not, you'll have to go to the menu, select 'Insert' and then 'Function.'
3. Utilize the function INDIRECT the same way as other functions for a direct cell reference. If you want the content of cell B6 to echo the content of cell C5, your command for B6 in the function text box will look like this: INDIRECT(C5)
4. Use INDIRECT nested in brackets for in-depth cell referencing. For example, to use INDIRECT to make versatile values within a SUM function, you would use SUM on the 'outside' and INDIRECT nested in parentheses, for a command like this: SUM (INDIRECT (A1, A2)).
5. Pass parameters to an INDIRECT statement for 'on the fly' cell changes. This step gets a little complicated. The thing to remember is that when you are passing values into an INDIRECT statement, you use ampersands to turn strings into references. Here's how it works: if you want B1 and B2 to contain the cell values for one of the A row cells, you put those into the B1 and B2 cells. Then, you create a statement like this: INDIRECT ('A'B1':A'B2). If this seems totally unclear, look at it carefully. In this case, INDIRECT is working like a computer programming function. The Excel function will 'see' the combination of text in quotations and cell references and interpret it to pass the cell you're building the values of the A row cells you selected and put into B1 and B2.

Blogger news