Reference functions are those that are used to refer to other cells in a given sheet. In this article we will talk about two important reference functions- ADDRESS and INDIRECT.
Reference Functions – ADDRESS
Preetam has an excel sheet. He wants to make a reference to another cell in one of his cells. How can he do this? It can be easily done using the ADDRESS function.
Syntax:
ADDRESS(row_num,col_num,[abs_num],[a1],[sheet])
Where, ‘row_num’ stands for the sow number of the cell reference he wants to make, ‘col_num’ refers to the column number of the cell reference that he wants to make, ‘abs_num’ refers to the type of cell reference to be made that is if it should be an absolute reference or a relative reference, ‘a1’ refers to the reference style and ‘sheet’ is indicative of the sheet in which the cell reference is present (if this is not mentioned, Excel assumes it to be on the current sheet).
Say Preetam wants to make a cell address for the cell B19 of Sheet 1 in the absolute cell reference format of A1 referencing style, then, the formula he should use is
=ADDRESS(19,2,1,1,1)
Here, for ‘abs_num’, the options available for input are:
1 – Absolute Reference
2- Absolute row /Relative column Reference
3- Absolute column/ Relative row Reference
4- Relative Reference
The input options available for ‘a1’ are:
0 – R1C1 Style
1 – A1 Style
The result will appear as follows.
Alternatively, he can choose the ‘ADDRESS’ option from the ‘Lookup and Reference’ list in the ‘Function Library’ of ‘Formulas’ menu. The following dialogue box will appear.
He can enter the cell references or choose them by clicking on the arrow mark beside and then click ‘OK’.
Reference Functions – INDIRECT
This option can be used to return a cell reference from the text given in a cell. For example, he has entered the cell reference in cell A1. Now suppose in cell C5 you enter the text ‘A1’. He can retrieve the contents of cell A1 by using this function.
Syntax:
INDIRECT(ref_num,[a1])
Where, ‘ref_num’ is the cell that contains the reference to the cell the data in which we want to display and ‘a1’ refers to the referencing style.
He should use the following formula
=INDIRECT(C5,1)
The input options available for ‘a1’ are:
0 – R1C1 Style
1 – A1 Style
The result will appear as follows.
Alternatively, he can choose the ‘INDIRECT’ option from the ‘Lookup and Reference’ list in the ‘Function Library’ of ‘Formulas’ menu. The following dialogue box will appear.
He can enter the cell references or choose them by clicking on the arrow mark beside and then click ‘OK’.