I want to use a conditional format to add 'Data Bars' to a portion of a table column. I created a formula with OFFSET() to set the correct range and gave the OFFSET formula a defined name. When I use the name in the Applies to field for the conditional format, it gets changed to an absolute range. The formatting won't apply to rows with new values even though the OFFSET function works correctly. If I go back into the conditional formatting dialog and retype the defined name, the new absolute range is entered.
Start = MATCH(Table1[Date], BillDate1, 0)
Length = BillDate2 - BillDate1
Range = OFFSET(Table1[Usage], Start, 0, Length, 1)
Applies to : =Range
and this gets stored as something like Applies to =$C$5:$C$34
Start = MATCH(Table1[Date], BillDate1, 0)
Length = BillDate2 - BillDate1
Range = OFFSET(Table1[Usage], Start, 0, Length, 1)
Applies to : =Range
and this gets stored as something like Applies to =$C$5:$C$34
