can a range be made dynamic in Excel 2007/2010
Posted on 2012-03-28
Say I have a formula (=COUNTIF($F4:$I4,"<>")=0) for conditional formatting that applies to a well defined range (e.g., =$F$4:$I$34). That range as defined is fixed.
Is it possible to vary the ending row (harcoded as I$34) dynamically?
I am thinking that some cell A1=40 could contain the number of rows to process in the spreadsheet. and that the range could somehow pickup cell A1 (containing integer 40) and somehow update the range automatically. Something like =$F$4:$I$[stick in the value of cell A1 automatically somehow, so to affect the range to $F$4:$I$40 dynamically]
Appreciate your insights.
Thanks and best regards,