A Formula In This Workbook Contains One Or More Invalid References

[darthrader]

Hello,

I have a rather complicated workbook with many VBA formulas. When I close it down a window displays "A formula in this workbook contains one or more invalid references". verify that your workbook contains a valid path, workbook, range name, and cell reference.

This only happens when I close the workbook. I seem to have all functionality. How can I find the invalid reference.

Thanks

[PCI]

The problem is difficult to appreciate without example.
A way to procede is to remove step by step some parts or sheets of the file and see what 's happen, it's long way ...
Could you send us something.

[darthrader]

Hi,

The code is somewhat restricted.

It basically opens up a bunch of data files and analyzes the data. It opens 22 extertal files and imports them to a summary sheets with graphs.

It saves the output as a temp file. It is this temp file that shows the error when I close it. It has all of the functionality it is suppose to have. The error only happens the first time I save the file. If I open the file again later I do not get the message. It is mostly an annoyance. I think it started with the upgrade to Office 2003. I do not know if this helps.

I thought someone may have a way to test all of the refences in the output file. I think it would take forever to test them individually. I saw post which said hit cntr-G and then search for formula errors. This did not find anything.

I downloaded a file called findlink but it does not do anything.

[Dave Hawley]

You MUST be on the correct sheet when using Special Cells (F5) to locate formula errors. Or, group the Worksheets and search for #REF! or other Formula Errors

Search Here

[shg]

Check names on all sheets for #REF! errors as well.

[Dave Hawley]

Good thinking 99 Also check Chart references.

[shg]

Also check Chart references.

Better yet.

[darthrader]

I appreciate the feedback, however I can't seem to find any formula errors.

Am I doing this correctly?
I select all sheets from the sheet TABS at the bottom of the workbook.
I then click CNTRL-G and select special, then search for formula errors. Nothing is showing up as an error.

When I close Excel it tells me I have an invalid reference though.

[Dave Hawley]

You can only group the sheets for the Edit>Find. For SpecialCells you need to do each sheet individually.

[roderickkranendonk]

Most likely the problem is not in any formula's in your workbook, but probably in errors in named ranges. When copying or deleting sheets named ranges often get error references. Best way to figure out which named range is giving the error is by using the add in Name Manager
And then check Type: with errors.
Good luck