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!
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.
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(...).
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!
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 & rowNumber).Value
' Now do something with left_cell_value
You could use a VBA script that changes the conditional formatting of a selection (you might have to adjust the condition & formatting accordingly):
For Each i In Selection
i.FormatConditions.Delete
i.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=" & i.Offset(0, -1).Address
With i.FormatConditions(1).Font
.Bold = True
End With
Next i
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.
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.
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)
=SUM(A1:OneCellAbove)
As this was the only way to answer @zipper
The reason why your formula:
="OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)=""xyz"""
doesn't conditionally format, is because the formula litteraly asks ="THIS PHRASE" is equal to "THAT PHRASE", try writing it like this:
=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="xyz" where "xyz" is your Text and "-1" is the number of columns to the left you want to search.
=INDIRECT("RC[1]",0)– prograhammer