Why can't I nest the ADDRESS() formula inside the ROW() formula in Excel?

Question

=ADDRESS(3,1)  'evaluates to $A$3

=ROW($A$3)     'evaluates to 3

Why can't I nest them?

=ROW(ADDRESS(3,1)) 'Gives an error.

Answer 1

instead of using ADDRESS which returns a string, consider using INDEX which will return a cell reference. The general format of INDEX is:

INDEX(Range you want to look in, rows down from top row, columns right for first column)

so in order to reference your whole sheet like address would you would need to select the range of the entire sheet:

  =INDEX($A$1:$XFD$1048576,3,1)

The above formula actually returns the cell reference of $A$3 ($ is due to 3 and 1 being hard coded) then turns around and displays the contents of $A$3. As a result you don't actually see the $A$3. On the interesting side of things it also means you can define a range with INDEX(...):INDEX(...). To finish off your formula you would nest the INDEX in your ROW function as follows:

=ROW(INDEX($A$1:$XFD$1048576,3,1))

This avoids the use of the volatile function of INDIRECT and some of its other restrictions.

Answer 2

Try:

=ROW(INDIRECT(ADDRESS(3,1)))