INDIRECT Function (Functions in Excel)

	Jan	Feb	Mar
North	10	20	30
South	40	50	60
East	70	80	90
West	100	110	120

Type address of any of the cells in the above table, such as G6 :	G6
The value in the cell you typed is :	80	=INDIRECT(H9)

What Does It Do ?

This function converts a plain piece of text which looks like a cell address into a usable

cell reference.

The address can be either on the same worksheet or on a different worksheet.

Syntax

=INDIRECT(Text)

Formatting

No special formatting is needed.

Example 1

This example shows how data can be picked form other worksheets by using

the worksheet name and a cell address.

The example uses three other worksheets named NORTH, SOUTH and EAST.

The data on these three sheets is laid out in the same cells on each sheet.

When a reference to a sheet is made the exclamation symbol ! needs to be placed

between the sheet name and cell address acting as punctuation.

Type the name of the sheet, such as North :	North
Type the cell to pick data from, such as C8 :	C8
The contents of the cell C8 on North is :	120	=INDIRECT(G33&"!"&G34)

The =INDIRECT() created a reference to =NORTH!C8

Example 2

This example uses the same data as above, but this time the =SUM() function is

used to calculate a total from a range of cells.

Type the name of the sheet, such as South :	South
Type the start cell of the range, such as C5 :	C5
Type the end cell of the range, such as C7 :	C7
The sum of the range C5:C7 on South is :	1200
	=SUM(INDIRECT(G44&"!"&G45&":"&G46))

The =INDIRECT() created a reference to =SUM(SOUTH!C5:C7)

Related Posts by Categories