Self-reference for cell, column and row in worksheet functions

Question

In a worksheet function in Excel, how do you self-reference the cell, column or row you're in?

Answer 1

For a cell to self-reference itself:

INDIRECT(ADDRESS(ROW(), COLUMN()))

For a cell to self-reference its column:

INDIRECT(ADDRESS(1,COLUMN()) & ":" & ADDRESS(65536, COLUMN()))

For a cell to self-reference its row:

INDIRECT(ADDRESS(ROW(),1) & ":" & ADDRESS(ROW(),256))
or
INDIRECT("A" & ROW() & ":IV" & ROW())

The numbers are for 2003 and earlier, use column:XFD and row:1048576 for 2007+.

Note: The INDIRECT function is volatile and should only be used when needed.