Using a Dynamic Ranges and a if statement
Posted by Mike on February 15, 2002 5:44 AM
I'm using a dynamic range that contains an if statement,
The if statement returns "", if false and (x)+1, if true. This hoses the ability of the dynamic range as it counts the "" cell as a value. Any help here would be appreciated.
Posted by Mark W. on February 15, 2002 7:59 AM
What's the formula for your dynamic range? [nt]
Posted by mike on February 16, 2002 8:01 AM
:I may have misstated, the "if" statement is within the column of data the dynamic range refrences.
dynamic range formula =
=OFFSET(data!$AG$11,0,0,COUNTA(data!$AE$11:$AE$953),1)
It refrences a column with an if statemnt that returns "" if the extrenal data source returns nothing and (x)+1 when the data source returns a value.
example
AD23 = 02/16/2002
AD24 = ""
X = cell ae23 (date value)02/16/2002
Cell AE24 = ""
the problem is the dynamic range is counting the "" as a value and extending the range to wherever the formula stops.
Is the a way to specify the counta function perhaps with using a refrenced cell(within the offset formula) in the worksheet that does a count of real numbers in the range ?
Posted by mike on February 18, 2002 10:23 AM
I may have misstated the problem, the "if" statement is within the column of data the dynamic range refrences.
dynamic range formula =
=OFFSET(data!$AG$11,0,0,COUNTA(data!$AE$11:$AE$953),1)
It refrences a column with an if statemnt that returns "" if the extrenal data source returns nothing and (x)+1 when the data source returns a value.
example
AD23 = 02/16/2002
AD24 = ""
X = cell ae23 (date value)02/16/2002
Cell AE24 = ""
the problem is the dynamic range is counting the "" as a value and extending the range to wherever the formula stops.
Is the a way to specify the counta function perhaps with using a refrenced cell(within the offset formula) in the worksheet that does a count of real numbers in the range ?
