Use Of INDIRECT
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 :
|
#REF!
|
=INDIRECT(G33&"!"&G34)
|
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 :
|
#REF!
|
The =INDIRECT() created a reference to =SUM(SOUTH!C5:C7)
कोई टिप्पणी नहीं:
एक टिप्पणी भेजें