Formulas can contain numbers, like 5 or 8, but more often they reference the contents of cells. A cell reference tells Sheets where to look for values you want to use in a formula. For example, the formula =A5+A6 adds the values in cells A5 and A6.
Using cell references is useful because if you change the values in the referenced cells, the formula result automatically updates using the new values. There are two different ways to refer to other cells while creating a formula—relative references and absolute references. Let's take a look at the difference between the two.
A relative cell reference is the default type.
- Relative References change when a formula is copied to another cell.
- B4 is an example of a relative cell reference.
When a formula is copied, relative references update the formula based on the cell’s location.
An absolute cell reference ensures that the formula is always refers to the same cell, even when it's copied and pasted.
I have conditional formatting relying on a date where the cell is merged because I had to add another row in order to have the sub contractors listed separately. For example, I have rows 2 and 3 for a task and columns A, B, C, D, E, F, G, H, I, J, K, L, M, N, O and P are all merged however once you get to column Q, R, S, T, U they are no longer merged to show the distinction between the 2 different sub contractors on the task. Column V is merged again. My problem is Column I which is the expiration date of the task is the condition to turn all the cells to the color requested. Because column I is merged the first subcontractor in Row 2, Column Q, R, S, T, U will turn the color but the second sub contractor in Row 3, Column Q, R, S, T and U will not turn the color requested. It will only happen if I don't merge the Column I which has the expiration date and I put the date in both cells [2 and 3]. PLEASE HELP IVE BEEN WORKING ON THIS FOR 3 DAYS!!!!!!
ngha says:
May 6, 2015 at 8:39 am
Hi,
I have a question:
If I have a table as follows:
ROW1: Name Tasks Mon Tue Wed Thu Fri
Row2: A clean 4.5 3.5 8 6 9
Row3 B Clerk 9 4 5.5 7 0
Row4: C Sale 5.5 7 6 8 1
Row5: A Sale 3.5 0.5 0 1 0
Row6: B Clean 0 0 0.5 0 0
How do I use conditional format to set the color to the cells in each column Mon to Fri with the condition as follows?
if total hours of each person in a day