Hi again Ken,

I thought later why not use COUNTA and count the cells that actually have
data in them?

Regards,

OssieMac


"Ken Johnson" wrote:

On Aug 11, 4:16 pm, OssieMac
wrote:
Hi Ken,

Doesn't work in xl2002 either. However does work in xl2007. However, the
answer to your problems will probably be solved if you use the following:-

COUNTBLANK($C$C)

Regards,

OssieMac





"Ken Johnson" wrote:
I used the following formula to define a named range for a chart...

=OFFSET(Sheet1!$C$4,1,0,ROWS(Sheet1!$C:$C)-COUNTIF(Sheet1!$C:$C,"")-4)

On my home PC and both PCs in my staffroom (all Excel 2003) everything
worked perfectly.

However, on a laptop (Excel 2000), the range plotted by the chart was
wrong and I kept getting a message about having more than 32000
points.

Thinking it was a 2000 versus 2003 difference, I swapped to another
laptop with Excel 2003, but the problem remained.

I tracked the problem down to the COUNTIF(Sheet1!$C:$C,"") formula.
On my home and staffroom PCs it counted cells that were blank and
cells rendered blank ("") by an IF formula.

I've had to change the IF formula so that cells I don't want in the
named range are changed to "NO" instead of "" and the formula that
defines the named range is now...

=OFFSET(Sheet1!$C$4,1,0,COUNTIF(Sheet1!$C$5:$C$100 5,"<NO")-1)

Does anybody know why COUNTIF(Sheet1!$C:$C,"") would produce different
results on different machines?

Ken Johnson

Hi OssieMac,

I tried your suggestion and it works well.

Thanks for that.

Ken Johnson