Excel Reference To Current Cell

Question

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

User · Answer

You could use   CELL  width   INDIRECT ADDRESS ROW    COLUMN

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 A1 in any worksheet  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    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

User · Answer

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

User · Answer

Create a named formula called THIS CELL  In the current worksheet  select cell A1  this is important    Open Name Manager  Ctl F3   Click New     Enter  quot THIS CELL quot   or just  quot THIS quot   which is my  preference  into Name   Enter the following formula into Refers to    A1 NOTE  Be sure cell A1 is selected  This formula is relative to the ActiveCell   Under Scope  select Workbook   Click OK and close the Name Manager   Use the formula in the worksheet exactly as you wanted  CELL  quot width quot  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   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  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

User · Answer

ADDRESS ROW   COLUMN     ADDRESS ROW   COLUMN   1   ADDRESS ROW   COLUMN   2   ADDRESS ROW   COLUMN   3   ADDRESS ROW   COLUMN   4

User · Answer

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

User · Answer

Without INDIRECT     CELL  width   OFFSET  A 1 ROW  -1 COLUMN  -1

User · Answer

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  -

[excel] Excel Reference To Current Cell

Examples related to excel

Examples related to worksheet-function