OFFSET function in named range returning wrong # of rows

[Guest]

I am trying to create a dynamically named range. However, the range keeps
selecting too many cells.

This function: =COUNTIF(PivotMONTH!$I:$I,">0") returns 47, which is the
correct number of rows.

However, this named range which contains the above function:
=OFFSET(PivotMONTH!$G$4,0,0,(COUNTIF(PivotMONTH!$I:$I,">0")),5) returns a
range 58 rows long. My data does indeed have 58 rows, but the last few rows
are just zeroes. My COUNTIF function should eliminate those rows. Why does
my countif return the correct number of rows when it stands alone, but an
incorrect number when it is used in the named range?

Thanks!
Heidi

 

[Guest]

What are you trying to do with OFFSET? OFFSET has the following arguments:
=OFFSET(reference,rows,cols,height,width) of which arguments the first three
are required. With your formula
=OFFSET(PivotMONTH!$G$4,0,0,(COUNTIF(PivotMONTH!$I:$I,">0")),5) it looks like
your nested COUNTIF is the height argument of OFFSET

Assuming your intentions square with the above, which column do the 0 values
occur in?

Dave

 

[Guest]

Dave,

Thanks for replying! My data are in columns I, J and K. The entire range
should include columns G-K and as many rows as there are lines of data, not
including rows of zeroes at the end. Currently, there are only 47 rows that
contain numbers in I- the remaining rows contain zeroes. They will be
filled in as time goes on.

I'd like to create a dynamically named range that lets me keep an updated
graph going without having to reset the "source data" for the graph each time
new data is added.

I've done this with other ranges of data, but this one with the zeroes at
the end is causing more problems...

Thanks,

Heidi

 

[Guest]

Heidi,
Worked OK for me on a simple test. I added /deleted rows with 0s
and displayed the correct range.

XL2003

 

[Guest]

Well, I tried your formula and it works for me, and it works for Toppers as
well, so something about your data seems wrong.

Are the 0s formatted as numbers or text?

Dave

 

[Guest]

I think I figured out the problem.

I tried it in a new workbook and it worked as expected, as both you and
Toppers found too. I think the problem is that column G contains merged
cells. They are merged to make a prettier X-axis labels in the graph that
is generated, but obviously that appears to be messing up my auto-updating
selection.

I'll work on a different way to organize the data so that isn't a problem.

Thanks for your help!

Heidi