Excel formula to reference CELL TO THE LEFT

Question

I m trying to do conditional formatting so that the cell color will change if the value is different from the value in the cell left of it  each column is a month  in each row are the expenses on certain object  I want to monitor easily changes in prices over months    I can do it per cell and format-drag it  but I would like a general formula to apply to the whole worksheet   Thanks

User · Answer

Instead of writing the very long     OFFSET INDIRECT ADDRESS ROW    COLUMN     0 -1    You can simply write     OFFSET  Name of your Cell  0 -1    Thus for example you can write into Cell B2    OFFSET B2 0 -1    to reference to cell B1  Still thanks Jason Young   I would have never come up with this solution without your answer

User · Answer

I stumbled upon this thread because I wanted to always reference the  cell to the left  but CRUCIALLY in a non-volatile way  no OFFSET  INDIRECT and similar disasters    Looking the web up and down  no answers    This thread does not actually provide an answer either    After some tinkering about I stumbled upon the most astonishing method  which I like to share with this community   Suppose a starting value of 100 in E6   Suppose I enter a delta to this value in F5  say 5   We would then calculate the continuation value  105  in F6   E6 F5   If you want to add another step  easy  just copy column F to column G and enter a new delta in G5     This is what we do  periodically   Each column has a date and these dates MUST BE in chronological order  to help with MATCH etc    Every so often it happens that we forget to enter a step   Now suppose you want to insert a column between F and G  to catch up with your omission  and copy F into the new G  to repopulate the continuation formula    This is NOTHING SHORT of a total disaster   Try it - H6 will now say  F6 H5 and NOT  as we absolutely need it to   G6 H5   The new G6 will be correct    To make this work  we can obfuscate this banal calculation in the most astonishing manner F6 index  E6 F6 1 columns  E1 F1 -1  F5   Copy right and you get G6 index  E6 G6 1 columns  E1 G1 -1  G5   This should never work  right   Circular reference  clearly   Try it out and be amazed   Excel seems to realize that although the INDEX range spans the cell we are recalculating  that cell itself is not addressed by the INDEX and thus DOES NOT create a circular reference   So now I am home and dry   Insert a column between F and G and we get exactly what we need   The continuation value in the old H will refer back to the continuation value we inserted in the new G

User · Answer

As this was the only way to answer  zipper The reason why your formula    quot OFFSET INDIRECT ADDRESS ROW    COLUMN     0 -1   quot  quot xyz quot  quot  quot   doesn t conditionally format  is because the formula litteraly asks   quot THIS PHRASE quot  is equal to  quot THAT PHRASE quot   try writing it like this   OFFSET INDIRECT ADDRESS ROW   COLUMN     0 -1   quot xyz quot  where  quot xyz quot  is your Text and  quot -1 quot  is the number of columns to the left you want to search

User · Answer

You could use a VBA script that changes the conditional formatting of a selection  you might have to adjust the condition  amp  formatting accordingly    For Each i In Selection i FormatConditions Delete i FormatConditions Add Type  xlCellValue  Operator  xlLess  Formula1       amp  i Offset 0  -1  Address With i FormatConditions 1  Font      Bold   True End With Next i

User · Answer

When creating your conditional formatting  set the range to which it applies to what you want  the whole sheet   then enter a relative formula  remove the   signs  as if you were only formatting the upper-left corner   Excel will properly apply the formatting to the rest of the cells accordingly   In this example  starting in B1  the left cell would be A1  Just use that--no advanced formula required     If you re looking for something more advanced  you can play around with column    row    and indirect

User · Answer

When creating a User Defined Function  I found out that the other answers involving the functions OFFSET and INDIRECT cannot be applied   Instead  you have to use Application Caller to refer to the cell the User Defined Function  UDF  has been used in  In a second step  you convert the column s index to the corresponding column s name  Finally  you are able to reference the left cell using the active worksheet s Range function   Function my user defined function argument1  argument2        Way to convert a column number to its name copied from StackOverflow       http   stackoverflow com a 10107264       Answer by Siddarth Rout  http   stackoverflow com users 1140579 siddharth-rout        License  if applicable due to the small amount of code   CC BY-SA 3 0     colName   Split Cells    Application Caller 1  Column - 1   Address       1       rowNumber   Application Caller 1  Row      left cell value   ActiveSheet Range colName  amp  rowNumber  Value        Now do something with left cell value

User · Answer

You could use a VBA script that changes the conditional formatting of a selection  you might have to adjust the condition  amp  formatting accordingly    For Each i In Selection i FormatConditions Delete i FormatConditions Add Type  xlCellValue  Operator  xlLess  Formula1       amp  i Offset 0  -1  Address With i FormatConditions 1  Font      Bold   True End With Next i

User · Answer

When creating your conditional formatting  set the range to which it applies to what you want  the whole sheet   then enter a relative formula  remove the   signs  as if you were only formatting the upper-left corner   Excel will properly apply the formatting to the rest of the cells accordingly   In this example  starting in B1  the left cell would be A1  Just use that--no advanced formula required     If you re looking for something more advanced  you can play around with column    row    and indirect

User · Answer

fill the A1 cell  with the following formula       IF COLUMN A1  1    OFFSET A20 0 -1   amp  1    Then autoextend to right  you get    1   A     B     C     ect ect  2     1    11   111   ect ect   If offset is outside the range of the available cell  you get the  REF  error   Hope you enjoy

User · Answer

When creating a User Defined Function  I found out that the other answers involving the functions OFFSET and INDIRECT cannot be applied   Instead  you have to use Application Caller to refer to the cell the User Defined Function  UDF  has been used in  In a second step  you convert the column s index to the corresponding column s name  Finally  you are able to reference the left cell using the active worksheet s Range function   Function my user defined function argument1  argument2        Way to convert a column number to its name copied from StackOverflow       http   stackoverflow com a 10107264       Answer by Siddarth Rout  http   stackoverflow com users 1140579 siddharth-rout        License  if applicable due to the small amount of code   CC BY-SA 3 0     colName   Split Cells    Application Caller 1  Column - 1   Address       1       rowNumber   Application Caller 1  Row      left cell value   ActiveSheet Range colName  amp  rowNumber  Value        Now do something with left cell value

User · Answer

I think this is the easiest answer   Use a  Name  to reference the offset   Say you want to sum a column  Column A  all the way to  but not including  the cell holding the summation  say Cell A100   do this    I assume you are using A1 referencing when creating the Name  R1C1 can subsequently be switched to    Click anywhere in the sheet not on the top row - say Cell D9 Define a Named Range called  say  OneCellAbove   but overwrite the  RefersTo  box with   D8   no quotes  Now  in Cell A100 you can use the formula  SUM A1 OneCellAbove

User · Answer

You could use a VBA script that changes the conditional formatting of a selection  you might have to adjust the condition  amp  formatting accordingly    For Each i In Selection i FormatConditions Delete i FormatConditions Add Type  xlCellValue  Operator  xlLess  Formula1       amp  i Offset 0  -1  Address With i FormatConditions 1  Font      Bold   True End With Next i

User · Answer

fill the A1 cell  with the following formula       IF COLUMN A1  1    OFFSET A20 0 -1   amp  1    Then autoextend to right  you get    1   A     B     C     ect ect  2     1    11   111   ect ect   If offset is outside the range of the available cell  you get the  REF  error   Hope you enjoy

User · Answer

If you change your cell reference to use R1C1 notation  Tools Options  General tab   then you can use a simple notation and paste it into any cell   Now your formula is simply    RC -1

User · Answer

Make a named formula  quot LeftCell quot  For those looking for a non-volatile answer  you can accomplish this by using the INDEX function in a named formula   Select Cell A2  Open Name Manager  Ctrl F3   Click New  Name it  LeftCell   or whatever you prefer   For Scope   select Workbook  In Refers to   enter the formula   INDEX  A1  A2  1   Click OK and close Name Manager   This tells Excel to always look at the value immediately to the left of the current cell  and will change dynamically as different cells are selected  If the name is used alone it provides the cell s value  but in a range it uses the reference  Credit to this answer about cell references for the idea

User · Answer

When creating your conditional formatting  set the range to which it applies to what you want  the whole sheet   then enter a relative formula  remove the   signs  as if you were only formatting the upper-left corner   Excel will properly apply the formatting to the rest of the cells accordingly   In this example  starting in B1  the left cell would be A1  Just use that--no advanced formula required     If you re looking for something more advanced  you can play around with column    row    and indirect

User · Answer

I stumbled upon this thread because I wanted to always reference the  cell to the left  but CRUCIALLY in a non-volatile way  no OFFSET  INDIRECT and similar disasters    Looking the web up and down  no answers    This thread does not actually provide an answer either    After some tinkering about I stumbled upon the most astonishing method  which I like to share with this community   Suppose a starting value of 100 in E6   Suppose I enter a delta to this value in F5  say 5   We would then calculate the continuation value  105  in F6   E6 F5   If you want to add another step  easy  just copy column F to column G and enter a new delta in G5     This is what we do  periodically   Each column has a date and these dates MUST BE in chronological order  to help with MATCH etc    Every so often it happens that we forget to enter a step   Now suppose you want to insert a column between F and G  to catch up with your omission  and copy F into the new G  to repopulate the continuation formula    This is NOTHING SHORT of a total disaster   Try it - H6 will now say  F6 H5 and NOT  as we absolutely need it to   G6 H5   The new G6 will be correct    To make this work  we can obfuscate this banal calculation in the most astonishing manner F6 index  E6 F6 1 columns  E1 F1 -1  F5   Copy right and you get G6 index  E6 G6 1 columns  E1 G1 -1  G5   This should never work  right   Circular reference  clearly   Try it out and be amazed   Excel seems to realize that although the INDEX range spans the cell we are recalculating  that cell itself is not addressed by the INDEX and thus DOES NOT create a circular reference   So now I am home and dry   Insert a column between F and G and we get exactly what we need   The continuation value in the old H will refer back to the continuation value we inserted in the new G

User · Answer

Why not just use    ADDRESS ROW   COLUMN  -1

User · Answer

I think this is the easiest answer   Use a  Name  to reference the offset   Say you want to sum a column  Column A  all the way to  but not including  the cell holding the summation  say Cell A100   do this    I assume you are using A1 referencing when creating the Name  R1C1 can subsequently be switched to    Click anywhere in the sheet not on the top row - say Cell D9 Define a Named Range called  say  OneCellAbove   but overwrite the  RefersTo  box with   D8   no quotes  Now  in Cell A100 you can use the formula  SUM A1 OneCellAbove

User · Answer

Why not just use    ADDRESS ROW   COLUMN  -1

User · Answer

Please select the entire sheet and HOME   Styles - Conditional Formatting  New Rule     Use a formula to determine which cells to format and Format values where this formula is true         A1 lt  gt XFD1   Format     select choice of formatting  OK  OK

User · Answer

OFFSET INDIRECT ADDRESS ROW    COLUMN     0 -1

User · Answer

Please select the entire sheet and HOME   Styles - Conditional Formatting  New Rule     Use a formula to determine which cells to format and Format values where this formula is true         A1 lt  gt XFD1   Format     select choice of formatting  OK  OK

User · Answer

As this was the only way to answer  zipper The reason why your formula    quot OFFSET INDIRECT ADDRESS ROW    COLUMN     0 -1   quot  quot xyz quot  quot  quot   doesn t conditionally format  is because the formula litteraly asks   quot THIS PHRASE quot  is equal to  quot THAT PHRASE quot   try writing it like this   OFFSET INDIRECT ADDRESS ROW   COLUMN     0 -1   quot xyz quot  where  quot xyz quot  is your Text and  quot -1 quot  is the number of columns to the left you want to search

User · Answer

If you change your cell reference to use R1C1 notation  Tools Options  General tab   then you can use a simple notation and paste it into any cell   Now your formula is simply    RC -1

User · Answer

OFFSET INDIRECT ADDRESS ROW    COLUMN     0 -1

User · Answer

Even simpler    indirect address row    column   - 1     OFFSET returns a reference relative to the current reference  so if indirect returns the correct reference  you don t need it

User · Answer

Make a named formula  quot LeftCell quot  For those looking for a non-volatile answer  you can accomplish this by using the INDEX function in a named formula   Select Cell A2  Open Name Manager  Ctrl F3   Click New  Name it  LeftCell   or whatever you prefer   For Scope   select Workbook  In Refers to   enter the formula   INDEX  A1  A2  1   Click OK and close Name Manager   This tells Excel to always look at the value immediately to the left of the current cell  and will change dynamically as different cells are selected  If the name is used alone it provides the cell s value  but in a range it uses the reference  Credit to this answer about cell references for the idea

User · Answer

When creating your conditional formatting  set the range to which it applies to what you want  the whole sheet   then enter a relative formula  remove the   signs  as if you were only formatting the upper-left corner   Excel will properly apply the formatting to the rest of the cells accordingly   In this example  starting in B1  the left cell would be A1  Just use that--no advanced formula required     If you re looking for something more advanced  you can play around with column    row    and indirect

User · Answer

The shortest most compatible version is    INDIRECT  RC -1   0     RC -1   means one column to the left   R 1 C -1   is bottom-left   The second parameter 0 means that the first parameter is interpreted using R1C1 notation   The other options    OFFSET INDIRECT ADDRESS ROW    COLUMN     0 -1    Too long in my opinion  But useful if the relative value is dynamic derived from another cell   e g      OFFSET INDIRECT ADDRESS ROW    COLUMN     0  A1    The most simple option     RC -1    has the disadvantage that you need to turn on R1C1 notation using options  which is a no-go when other people have to use the excel

User · Answer

You could use a VBA script that changes the conditional formatting of a selection  you might have to adjust the condition  amp  formatting accordingly    For Each i In Selection i FormatConditions Delete i FormatConditions Add Type  xlCellValue  Operator  xlLess  Formula1       amp  i Offset 0  -1  Address With i FormatConditions 1  Font      Bold   True End With Next i

User · Answer

Instead of writing the very long     OFFSET INDIRECT ADDRESS ROW    COLUMN     0 -1    You can simply write     OFFSET  Name of your Cell  0 -1    Thus for example you can write into Cell B2    OFFSET B2 0 -1    to reference to cell B1  Still thanks Jason Young   I would have never come up with this solution without your answer

User · Answer

The shortest most compatible version is    INDIRECT  RC -1   0     RC -1   means one column to the left   R 1 C -1   is bottom-left   The second parameter 0 means that the first parameter is interpreted using R1C1 notation   The other options    OFFSET INDIRECT ADDRESS ROW    COLUMN     0 -1    Too long in my opinion  But useful if the relative value is dynamic derived from another cell   e g      OFFSET INDIRECT ADDRESS ROW    COLUMN     0  A1    The most simple option     RC -1    has the disadvantage that you need to turn on R1C1 notation using options  which is a no-go when other people have to use the excel

User · Answer

Even simpler    indirect address row    column   - 1     OFFSET returns a reference relative to the current reference  so if indirect returns the correct reference  you don t need it

[excel] Excel formula to reference 'CELL TO THE LEFT'

Examples related to excel

Examples related to excel-2007

Examples related to excel-formula