[excel] Excel Reference To Current Cell

How do I obtain a reference to the current cell?

For example, if I want to display the width of column A, I could use the following:

=CELL("width", A2)

However, I want the formula to be something like this:

=CELL("width", THIS_CELL)

Inside tables you can use [@] which (unfortunately) Excel automatically expands to Table1[@] but it does work. (I'm using Excel 2010)

For example when having two columns [Change] and [Balance], putting this in the [Balance] column:

=OFFSET([@], -1, 0) + [Change]

Note of course that this depends on the order of the rows (just like most any other solution), so it's a bit fragile.

Without INDIRECT(): =CELL("width", OFFSET($A$1,ROW()-1,COLUMN()-1) )

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. 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 folowing 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

I found the best way to handle this (for me) is to use the following:

Dim MyString as String
MyString = Application.ThisCell.Address
Range(MyString).Select

Hope this helps.

A2 is already a relative reference and will change when you move the cell or copy the formula.

You could use

=CELL("width", INDIRECT(ADDRESS(ROW(), COLUMN())))

=ADDRESS(ROW(),COLUMN())
=ADDRESS(ROW(),COLUMN(),1)
=ADDRESS(ROW(),COLUMN(),2)
=ADDRESS(ROW(),COLUMN(),3)
=ADDRESS(ROW(),COLUMN(),4)

=ADDRESS(ROW(),COLUMN(),4) will give us the relative address of the current cell. =INDIRECT(ADDRESS(ROW(),COLUMN()-1,4)) will give us the contents of the cell left of the current cell =INDIRECT(ADDRESS(ROW()-1,COLUMN(),4)) will give us the contents of the cell above the current cell (great for calculating running totals)

Using CELL() function returns information about the last cell that was changed. So, if we enter a new row or column the CELL() reference will be affected and will not be the current cell's any longer.

EDIT: the following is wrong, because Cell("width") returns the width of the last modified cell.

Cell("width") returns the width of the current cell, so you don't need a reference to the current cell. If you need one, though, cell("address") returns the address of the current cell, so if you need a reference to the current cell, use indirect(cell("address")). See the documentation: http://www.techonthenet.com/excel/formulas/cell.php

Several years too late:

Just for completeness I want to give yet another answer:

First, go to Excel-Options -> Formulas and enable R1C1 references. Then use

  =CELL("width", RC)

RC always refers the current Row, current Column, i.e. "this cell".

Rick Teachey's solution is basically a tweak to make the same possible in A1 reference style (see also GSerg's comment to Joey's answer and note his comment to Patrick McDonald's answer).

Cheers
:-)

Full credit to the top answer by @rick-teachey, but you can extend that approach to work with Conditional Formatting. So that this answer is complete, I will duplicate Rick's answer in summary form and then extend it:

1. Select cell A1 in any worksheet.
2. Create a Named Range called THIS and set the Refers to: to =!A1.

Attempting to use THIS in Conditional Formatting formulas will result in the error:

You may not use references to other workbooks for Conditional Formatting criteria

If you want THIS to work in Conditional Formatting formulas:

3. Create another Named Range called THIS_CF and set the Refers to: to =THIS.

You can now use THIS_CF to refer to the current cell in Conditional Formatting formulas.

You can also use this approach to create other relative Named Ranges, such as THIS_COLUMN, THIS_ROW, ROW_ABOVE, COLUMN_LEFT, etc.