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.

 

[JamesW]

Hi,

You cannot post images on this board. Can you tell us the error you are getting?

 

[doublej41]

The message reads:

"A formula within this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name, and cell reference."

 

[JamesW]

Hi,

Check each sheet for #REF errors in your formulas.

If you can't find any, create a backup of your workbook and delete 1 sheet, save it and see if it shows the error, if not rinse and repeat for the 2nd sheet and so on.

This should tell you which sheet the error is on, you can then try and work out which formula is causing the error.

Also, check the references for any charts you have in the workbook.

 

[jim may]

Try using the GoTo Special.. Dialog, selecting Errors

 

[doublej41]

Thanks for your replies.

I have singled out the sheet that is giving the error, however still cannot find the problem.

The search for #REF's on that sheet returns no results, most of my charts reference my dynamic ranges and they all work OK when there is data present and show nothing if I delete the data. I use =IF(ISERR( on all of my equations so that the formulas will return a 0 as opposed to an error.

Also the GoTo Special returns no cells when I search for formulas > Errors.

 

[Jonmo1]

Thanks for your replies.

I have singled out the sheet that is giving the error, however still cannot find the problem.

The search for #REF's on that sheet returns no results, most of my charts reference my dynamic ranges and they all work OK when there is data present and show nothing if I delete the data. I use =IF(ISERR( on all of my equations so that the formulas will return a 0 as opposed to an error.

Also the GoTo Special returns no cells when I search for formulas > Errors.

That does not prevent the formula from containing an invalid link, it just prevents the cell from displaying the REF Error.


And the GoTo Special won't find any errors, because you used =IF(ISERR

 

[JamesW]

You could also check Edit -> Links and see if there are any broken links.

 

[doublej41]

Thanks again for all the replies.

The file contains no links, the formulas do not reach outside of the sheet they are on. The Edit Links button is greyed out so there are no links that have been created inadvertently.

I have some ranges that are named the same, but have a different scope and reference. I have checked through these and made sure that the charts are using the range with the correct scope, which they all are. But could the fact they have the same name be causing an issue?

 

[Jonmo1]

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.