Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: A Formula In This Workbook Contains One Or More Invalid References

  1. #1
    Join Date
    21st July 2006
    Posts
    17

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    23rd October 2003
    Location
    France Alsace
    Posts
    3,519

    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

  3. #3
    Join Date
    21st July 2006
    Posts
    17

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

    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

  5. #5
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,322

    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.
    MS MVP - Excel

  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

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

    Good thinking 99 Also check Chart references.

  7. #7
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,322

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

    Also check Chart references.
    Better yet.
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  8. #8
    Join Date
    21st July 2006
    Posts
    17

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

    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.

  10. #10
    Join Date
    7th March 2008
    Posts
    1

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Possible Answers

  1. Replies: 2
    Last Post: July 19th, 2007, 19:10
  2. Replies: 7
    Last Post: May 24th, 2007, 20:43
  3. Replies: 14
    Last Post: September 30th, 2006, 23:45
  4. Replies: 3
    Last Post: July 27th, 2006, 14:05
  5. Replies: 1
    Last Post: May 13th, 2004, 04:48

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno