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.

Answer 2

where F13 is the cell you need to reference:

=CELL("Row",F13)  yields 13; its row number

=CELL("Col",F13)  yields 6; its column number;  

=SUBSTITUTE(ADDRESS(1,COLUMN(F13)*1,4),"1","") yields F; its column letter

Answer 3

I don't see the need for Indirect, especially for conditional formatting.

The simplest way to self-reference a cell, row or column is to refer to it normally, e.g., "=A1" in cell A1, and make the reference partly or completely relative. For example, in a conditional formatting formula for checking whether there's a value in the first column of various cells' rows, enter the following with A1 highlighted and copy as necessary. The conditional formatting will always refer to column A for the row of each cell:

= $A1 <> ""

Answer 4

There is a better way that is safer and will not slow down your application. How Excel is set up, a cell can have either a value or a formula; the formula can not refer to its own cell. Otherwise, You end up with an infinite loop, since the new value would cause another calculation... .

Use a helper column to calculate the value based on what you put in the other cell.

For Example:

Column A is a True or False, Column B contains a monetary value, Column C contains the following formula:

=B1

Now, to calculate that column B will be highlighted yellow in a conditional format only if Column A is True and Column B is greater than Zero...

=AND(A1=True,C1>0)

You can then choose to hide column C

Answer 5

For a non-volatile solution, how about for 2007+:

for cell    =INDEX($A$1:$XFC$1048576,ROW(),COLUMN())
for column  =INDEX($A$1:$XFC$1048576,0,COLUMN())
for row     =INDEX($A$1:$XFC$1048576,ROW(),0)

I have weird bug on Excel 2010 where it won't accept the very last row or column for these formula (row 1048576 & column XFD), so you may need to reference these one short. Not sure if that's the same for any other versions so appreciate feedback and edit.

and for 2003 (INDEX became non-volatile in '97):

for cell    =INDEX($A$1:$IV$65536,ROW(),COLUMN())
for column  =INDEX($A$1:$IV$65536,0,COLUMN())
for row     =INDEX($A$1:$IV$65536,ROW(),0)

Answer 6

In a VBA worksheet function UDF you use Application.Caller to get the range of cell(s) that contain the formula that called the UDF.

Answer 7

My current Column is calculated by:

Method 1:

=LEFT(ADDRESS(ROW(),COLUMN(),4,1),LEN(ADDRESS(ROW(),COLUMN(),4,1))-LEN(ROW()))

Method 2:

=LEFT(ADDRESS(ROW(),COLUMN(),4,1),INT((COLUMN()-1)/26)+1)

My current Row is calculated by:

=RIGHT(ADDRESS(ROW(),COLUMN(),4,1),LEN(ROW()))

so an indirect link to Sheet2!My Column but a different row, specified in Column A on my row is:

Method 1:

=INDIRECT("Sheet2!"&LEFT(ADDRESS(ROW(),COLUMN(),4,1),LEN(ADDRESS(ROW(),COLUMN(),4,1))-LEN(ROW()))&INDIRECT(ADDRESS(ROW(),1,4,1)))

Method 2:

=INDIRECT("Sheet2!"&LEFT(ADDRESS(ROW(),COLUMN(),4,1),INT((COLUMN()-1)/26)+1)&INDIRECT(ADDRESS(ROW(),1,4,1)))

So if A6=3 and my row is 6 and my Col is C returns contents of "Sheet2!C3"

So if A7=1 and my row is 7 and my Col is D returns contents of "Sheet2!D1"

Answer 8

I was looking for a solution to this and used the indirect one found on this page initially, but I found it quite long and clunky for what I was trying to do. After a bit of research, I found a more elegant solution (to my problem) using R1C1 notation - I think you can't mix different notation styles without using VBA though.

Depending on what you're trying to do with the self referenced cell, something like this example should get a cell to reference itself where the cell is F13:

Range("F13").FormulaR1C1 = "RC"

And you can then reference cells in relative positions to that cell such as - where your cell is F13 and you need to reference G12 from it.

Range("F13").FormulaR1C1 = "R[-1]C[1]"

You're essentially telling Excel to find F13 and then move down 1 row and up one column from that.

How this fit into my project was to apply a vlookup across a range where the lookup value was relative to each cell in the range without having to specify each lookup cell separately:

Sub Code()
    Dim Range1 As Range
    Set Range1 = Range("B18:B23")
        Range1.Locked = False
        Range1.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],DATABYCODE,2,FALSE),"""")"
        Range1.Locked = True
End Sub

My lookup value is the cell to the left of each cell (column -1) in my DIM'd range and DATABYCODE is the named range I'm looking up against.

Hope that makes a little sense? Thought it was worth throwing into the mix as another way to approach the problem.

Answer 9

Just for row, but try referencing a cell just below the selected cell and subtracting one from row.

=ROW(A2)-1

Yields the Row of cell A1 (This formula would go in cell A1.

This avoids all the indirect() and index() use but still works.

Answer 10

=row() 

or

=column()

should be enough (I am using 365 version)