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

User · Answer

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.

User · Answer

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  lt  gt

User · Answer

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

User · Answer

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  amp  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

User · Answer

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   amp LEFT ADDRESS ROW   COLUMN   4 1  LEN ADDRESS ROW   COLUMN   4 1  -LEN ROW     amp INDIRECT ADDRESS ROW   1 4 1      Method 2       INDIRECT  Sheet2   amp LEFT ADDRESS ROW   COLUMN   4 1  INT  COLUMN  -1  26  1  amp 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

User · Answer

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

User · Answer

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 gt 0    You can then choose to hide column C

User · Answer

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

User · Answer

For a cell to self-reference itself   INDIRECT ADDRESS ROW    COLUMN       For a cell to self-reference its column   INDIRECT ADDRESS 1 COLUMN     amp       amp  ADDRESS 65536  COLUMN       For a cell to self-reference its row   INDIRECT ADDRESS ROW   1   amp       amp  ADDRESS ROW   256   or INDIRECT  A   amp  ROW    amp    IV   amp  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

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

Examples related to excel

Examples related to worksheet-function

Examples related to self-reference