Conditional Formatting applied to Offset range

[dropkickweasel]

I am applying conditional formatting to each row based on a date in one cell in each row.
Dates are in Column G, starting at G2.
The number of rows in the dataset will vary.
I would like for the conditional formatting to apply to each row on the sheet and to update automatically as the dataset increases/decreases.

I have tried using OFFSET to make this work.

=OFFSET(Sheet2!$G$2,0,0,COUNTA(Sheet2!$G:$G)-1,1)

It works in the first instance, but doesn't apply to new rows added to the dataset.
It seems that Excel converts the offset formula to absolute references - $G$2:$G$8 - based on there being data in the first 8 rows.
When I add data to row 9, the conditional formatting does not automatically apply to this new row.

I cannot convert the dataset to a table as it is populated using a FILTER formula drawing from another table.
If I try to convert to a table, I get a #SPILL error.

My current solution is to apply the conditional formatting to $C:$C, and add an additional condition to leave blank cells unformatted.
Is there any downside to my current solution?
The dataset could (theoretically) be any number of rows, but is unlikely to go above 1000.

Thanks for any input.

 

[6StringJazzer]

It's not clear how you are trying to use the OFFSET formula. It returns a range. You cannot use a dynamic range as the "Applies To" range for conditional formatting.

Applying to $C:$C is fine depending on what your formatting rule is. If the rule is an array formula then it could degrade performance. What is the conditional formatting rule you want to use?

 

[dropkickweasel]

It's not clear how you are trying to use the OFFSET formula. It returns a range. You cannot use a dynamic range as the "Applies To" range for conditional formatting.

Applying to $C:$C is fine depending on what your formatting rule is. If the rule is an array formula then it could degrade performance. What is the conditional formatting rule you want to use?

I think the confirmation that I am unable to use a dynamic range as the "applies to" range for conditional formatting answers my question.

The conditional formatting rules will look to see how much time there is between today and the date entered on each row.
There will likely be 3-5 different rules in the format =G2<TODAY() and variations adding / subtracting time from TODAY().
These rules will be applied across 15 - 20 sheets.

With the information above is there any reason to think that using $G:$G as the "Applies to" range would cause any significant slowdown to the workbook?

 

[6StringJazzer]

TODAY() is a volatile function. This means it is recalculated every time there is any recalculation on a sheet. This means that your CF rule will be reevaluated for all Applies To cells every time anything causes any recalculation. For your case it might be better to select the likely maximum number of rows such as $G$1:$G$1000 (you said $C:$C earlier but then $G:$G so I'm going with G)

 

[dropkickweasel]

TODAY() is a volatile function. This means it is recalculated every time there is any recalculation on a sheet. This means that your CF rule will be reevaluated for all Applies To cells every time anything causes any recalculation. For your case it might be better to select the likely maximum number of rows such as $G$1:$G$1000 (you said $C:$C earlier but then $G:$G so I'm going with G)

Would putting '=TODAY()' into a cell and referencing that cell in the conditional formatting rule make any difference to how often the conditional formatting rules reevaluate?

My thinking is instead of having TODAY in up to 5 CF rules across up to 20 sheets, for a potential total of 100 instances, there would just be one instance of it and up to 100 CF rules pointing to that single instance.

Is there any logic in that, or would the frequency of reevaluation not change at all?

 

[6StringJazzer]

Yes, that occurred to me but I don't have enough experience doing it to say for sure. Even if you put TODAY in one cell, the rules will still have to be evaluated since they still depend on a cell with a volatile function. However, the function TODAY will only have to be evaluated once instead of once for every rule.

 

[xp15]

use range 1:1000, it may change to full range (A1:AZ1000, or something like that), but it will update as you add more lines or columns, so it will stay full range

 

[6StringJazzer]

it will update as you add more lines or columns, so it will stay full range

Yes as long as new rows or columns are insert within that range.