Error message

[doublej41]

Hi All

I keep the getting the error message in the image below. As far as I can tell it has only started appearing since I have added some dynamic ranges, which I then graph. It also only seems to appear if my worksheet contains no raw data and just my formulas. Could it be something to do with the dynamic ranges? I have done a search for #REF and no results were returned.

Is there a simple fix or is it something I am likely to have to live with?

Thanks for any help.

 

[doublej41]

Are you using Dynamic Named ranges, using COUNTA or something like that?
If that is pointing to a blank range, that will cause an invalid reference because counta would return 0.

I am using dynamic ranges that use COUNT.

 

[Jonmo1]

OK, there's the problem

If COUNT is referring to a blank range, it will result in 0.
And that will be an invalid reference (there is no column or row #0)

Explains why it works fine once you put your data in the sheet.

 

[doublej41]

OK - Thanks for helping me get to the source.

Here is my Dynamic Name Range reference:

=OFFSET('Kv Deviation (1)'!$AF$8;0;0;COUNT('Kv Deviation (1)'!$AF:$AF);1)

When there is no data entered all values in column AF are set to 0 using the IF(ISERR(. Is there a way to make this reference work when there is no data?

 

[Jonmo1]

Try

=OFFSET('Kv Deviation (1)'!$AF$8;0;0;MAX(1,COUNT('Kv Deviation (1)'!$AF:$AF));1)

 

[doublej41]

Many thanks jonmo1 - That has done the trick.

Can you explain what the addition of the MAX has done?

 

[Jonmo1]

MAX returns the largest of the referenced values...

MAX(1,50) = 50
MAX(1,0) = 1
MAX(88,38,120,55) = 120

So this insures that your OFFSET formula will ALWAYS use a number of at least 1, where it used to use 0

 

[doublej41]

One last questions; is there a way to manually refresh a sheet? Sometimes my conditional formatting will miss out a few rows and I need to refresh to get it all formatted correctly.

Once again, many thanks for your help.

 

[Jonmo1]

You're welcome..

Not sure I understand the question..
It sounds like you already have a method of "manually" refreshing the sheet...

anyway, sounds like a question for a new thread.