Excel Reference To Current Cell

Solution 1:

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
:-)

Solution 2:

Create a named formula called THIS_CELL

1. In the current worksheet, select cell A1 (this is important!)

2. Open Name Manager (Ctl+F3)

3. Click New...

4. Enter "THIS_CELL" (or just "THIS", which is my preference) into Name:

5. Enter the following formula into Refers to:

   =!A1

   NOTE: Be sure cell A1 is selected. This formula is relative to the ActiveCell.

6. Under Scope: select Workbook.

7. Click OK and close the Name Manager

Use the formula in the worksheet exactly as you wanted

=CELL("width",THIS_CELL)

EDIT: Better solution than using INDIRECT()

It's worth noting that the solution I've given should be preferred over any solution using the INDIRECT() function for two reasons:

1. It is nonvolatile, while INDIRECT() is a volatile Excel function, and as a result will dramatically slow down workbook calculation when it is used a lot.
2. It is much simpler, and does not require converting an address (in the form of ROW() COLUMN()) to a range reference to an address and back to a range reference again.

EDIT: Also see this question for more information on workbook-scoped, sheet dependent named ranges.

EDIT: Also see @imix's answer below for a variation on this idea (using RC style references). In that case, you could use =!RC for the THIS_CELL named range formula, or just use RC directly.

Solution 3:

You could use

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

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