Announcement

Collapse
No announcement yet.

A Formula In This Workbook Contains One Or More Invalid References

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • A Formula In This Workbook Contains One Or More Invalid References

    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

  • #2
    Re: One Or More Invalid References

    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.
    Triumph without peril brings no glory: Just try

    Comment


    • #3
      Re: One Or More Invalid References

      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.

      Comment


      • #4
        Re: A Formula In This Workbook Contains One Or More Invalid References

        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

        Comment


        • #5
          Re: A Formula In This Workbook Contains One Or More Invalid References

          Check names on all sheets for #REF! errors as well.
          Entia non sunt multiplicanda sine necessitate.

          Comment


          • #6
            Re: A Formula In This Workbook Contains One Or More Invalid References

            Good thinking 99 Also check Chart references.

            Comment


            • #7
              Re: A Formula In This Workbook Contains One Or More Invalid References

              Also check Chart references.
              Better yet.
              Entia non sunt multiplicanda sine necessitate.

              Comment


              • #8
                Re: A Formula In This Workbook Contains One Or More Invalid References

                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.

                Comment


                • #9
                  Re: A Formula In This Workbook Contains One Or More Invalid References

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

                  Comment


                  • #10
                    Re: A Formula In This Workbook Contains One Or More Invalid References

                    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

                    Comment


                    • #11
                      Re: A Formula In This Workbook Contains One Or More Invalid References

                      I tried the name manager, it did not find any errors. I will keep working on this. I have a section that deletes the code once it executes. I also delete some of the shapes on the main page. I can try hiding them instead.

                      Regards,

                      Comment


                      • #12
                        Re: A Formula In This Workbook Contains One Or More Invalid References

                        No need for any add-ins. Check ALL the places we have told you to check.

                        Comment


                        • #13
                          Re: A Formula In This Workbook Contains One Or More Invalid References

                          I'd the same problem. Delete the charts in your worksheet one by one and keep trying to save the file until you stop getting the message. Once done re-do the chart with the problem. It's worked for me.

                          Cheers!
                          Chetan

                          Comment


                          • #14
                            Re: A Formula In This Workbook Contains One Or More Invalid References

                            I've just encountered the same problem. I had deleted a row which caused some invalid #REFS!

                            I fixed these and couldn't find any more in the workbook or charts.

                            I was able to resolve the problem simply by changing a chart type and then changing it back again to the original type.

                            Hope this helps someone.

                            Comment

                            Working...
                            X