MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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 ?