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)
13 Answers
=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.
A2 is already a relative reference and will change when you move the cell or copy the formula.
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:
- Select cell
A1in any worksheet. - Create a Named Range called
THISand set theRefers 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:
- Create another Named Range called
THIS_CFand set theRefers 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.
Reference to a cell that include this formula (self reference): address(row();column())
E.g. getting the value of the cell above: indirect(address(row()-1;column()))
Or what the OP asked: =Cell(width;address(row();column()))
Related