ADDRESS and INDIRECT: Reference Functions in MS Excel

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.

ADDRESS INDIRECT 1Alternatively, 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.

ADDRESS INDIRECT 2He 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.

ADDRESS INDIRECT 3

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.

ADDRESS INDIRECT 4

He can enter the cell references or choose them by clicking on the arrow mark beside and then click ‘OK’.

Leave a Comment

Your email address will not be published. Required fields are marked *