how to avoid a circ. ref.

[stuck]

I have a chart that uses named ranges that are dynamic, i.e. the named ranges use the OFFSET function, e.g. one of them uses:
=OFFSET(sheet1!$H$16,0,0,COUNT(sheet1!$E:$E),1)

However, if the count of col E is zero (which it is until other bits of the sheet do their magic) that pops up a warning about a range being not valid.

As yet I've not figured out to get a value into col E if the count of col E is zero. I keep coming up against a circular ref. warning.

Any ideas?

Thanks,

Ken

[HansV]

How about

=OFFSET(Sheet1!$H$16,0,0,MAX(COUNT(Sheet1!$E:$E),1),1)

This will refer to cell H16 if column E is entirely blank.

[stuck]

I'll try that.

Meanwhile it turns out I can bury an IF inside the OFFSET, thus:
=OFFSET(sheet1!$H$16,0,0,IF(COUNT(sheet1!$E:$E)=0,1,COUNT(analyte_5!$E:$E)),1)
and that works.

Ken

[Rudi]

Hans's solution does exactly the same. (If no values counted, the MAX will be 1.)

[stuck]

Guess what? Hans' suggestion is just so much more elegant than mine.

I'll go with his idea.



Ken