Problem with "Excel found a problem with one or more formula references in this worksheet", unable to find error

[Craig1]

Morning Guys,
I have searched high and low for this and still unable to find the solution.

I have an auto update (every 5 mins) on a worksheet with charts where it takes info from other worksheets and other workbooks, when all the charts are populated I don't get an error, but if 2 charts don't have any info in I get the error message twice or 3 times if 3 charts are empty every time the worksheet updates. I have checked all formulas on all sheets and found no errors, checked named ranges and again found no errors, checked for if there was any old secondary axis and no errors, I even copied all the charts to another sheet to see if this did the trick (as suggested and worked for others when I googled the problem), no joy. Checked the links to the other workbooks and no errors.

Anyone any other idea's?

Craig.

 

[JLGWhiz]

Just based on what you described relating to some charts not populating, it might be that you need to use some IF functions with your formulas so that when there is no data, the formula is not expecting a return value. Since the formulas are not posted, I am just guessing, but that is the only logical explanation based on the symptoms.

 

[Craig1]

Hi JLGWhiz,

 

[pvr928]

Hi Craig1

In my experience this error occurs when there is a problem with a reference in a chart(ie a cell/range to which the chart was referencing no longer exists, or has moved), not with a worksheet formula.

Have a look at the source data in your charts (on any chart -> right click -> Select Data)

Cheers

pvr928

 

[Craig1]

Hi JLGWhiz,
The initial data is coming from a named range which is looking at IF functions etc and none of them have errors in!!

Craig

 

[Craig1]

Hi pvr928,
I have checked this out and this is when I get the error, I click on the range and that's when the error comes up, but only when there is no data to retrieve, if there is data I don't get any errors at all, so there's no errors it just doesn't like the fact the graph will be empty.

Craig.

 

[Craig1]

Just remembered, the cells the named range is looking at are to be left empty if there is no data to fill them in.

Craig.

 

[JLGWhiz]

Just remembered, the cells the named range is looking at are to be left empty if there is no data to fill them in.

Craig.

Is this the scenario?
So when you click the range you apparently have code that tells it to populate the chart series, which uses a formula to define the series values. It finds no values and produces the message that there is a problem, since it found nothing to fill the series. If you are doing this with code, then you can have the code check the range of cells for values first and if none, then exit the sub and avoid the message.

 

[Craig1]

Hi JLGWhiz,
Basically that's it. The only thing is it's not done with code. It's all done with formula's and named ranges. The main thing I don't understand is that if there is info to populate the chart there are no errors, this error only comes through when no data is available, i.e production line is shutdown and no info is required.