The answer to the question you linked contains the answer to this one. But since it apparently didn't get through, here it is again:
In conditional formatting, you enter the formula as it should apply to the upper left corner of the formatted range. The spreadsheet will apply it to the rest of the range using relative references. So, if the upper left corner of formatted range is C4, then
=B4
means "the value in the cell to the left of this one".
=$B4
means "the value of the cell in B column of this row"
=C$1
means "the value at the top of this column"
=$B$2
means "the value in cell B2"
I don't think there is any built-in function that can access the formula in a cell.
If you want to go that route, you can use Apps Script function getFormula()
.
My recommendation is that you adopt the following convention for yourself.
As much as possible,
always organize your data in two dimensions: each row is a new instance of your data type, each column is an attribute of your data type.
If you do that, you can easily apply Conditional Formatting, among many other things, based on column index. You can also apply Conditional Formatting based on the strings in the first row, ie. your header.
In your example, there should be 3 columns. Each column contains
ID/name of share;
share type (or a formula to output share based on the ID/name of your share);
and total received, which is a formula that computes your "total received" based on share type
respectively.
You can use arrayformula
to apply your formula automatically and centralize your formulas in one cell. For example, in C2
, enter
=arrayformula(if(isblank(A2:A100,,ifs(exact(B2:B100,"Share Type 1"),A2:A100*0.8,
exact(B2:B100,"Share Type 2"),A2:A100*0.9))
Use B2:B100
or some relatively small number instead of B2:B
to reduce run time. The isblank()
check will ensure that the formula is only applied to the rows you entered share information on.
The custom formula for Conditional Formatting would be
=if(isblank(c2),false,exact(B2,"Share Type 1"))
with range C2:C
for one color and duplicate the same thing for "Share Type 2" for another color.
There are a few things you can do in order to conveniently enter/output the type of the share. For example,
you can use Data Validation and then enter the type manually. Data Validation will help you enter the correct string every time and it also comes with a simple complete-what-you-type function;
or, if the type of a given share is fixed over time, you can have a list of all shares on a separate tab/sheet and each type of shares under a different column. Also enter the associated coefficients there using arrayformula
. Then use vlookup
on your main tab/sheet to retrieve those coefficients conveniently.
Best Answer
First of all, sharing your sheet or at least a copy/example is going to get you a much better answer more quickly.
I attempted to recreate your spreadsheet as best I could given your description (completely guessing on the calendar sheet).
Based on my example, all the cells in
Calendar!B6:X37
contain a unique date. Each cell takes its date, and searches for it in'Training Classes'!E:E
. If it finds a match, then that cell gets highlighted yellow.The formula I use for the conditional formatting is:
The formula you were using:
I believe this wasn't working because: You said the calendar was in
B6:X37
, yet you referenceA1
(your working formula example usesS27
). Also, you are missing an=
at the start of the formula, and the>1
appears to be unnecessary.