Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

A formula in this worksheet contains one or more invalid references

Posted on 2009-05-17
5
1,402 Views
Last Modified: 2012-05-07
I am using excel 2007 and I have several macros, vba code and formulas.  I will save my workbook and have no problems.  Then when I reopen it, it will give me the error message: "a formula in this worksheet contains one or more invalid references".  Then I if I don't change anything and reopen it, half of the time it does not give me this error message.  I have tried to identify the issues in the worksheets by doing what other people have said.  This includes: hitting F5 and checking the formulas, searching for #REF!, and using vba code to identify the errors.  Can anyone tell me how to get rid of this or how to figure out where the error is?  Thanks.
0
Comment
Question by:jeffchmielinski
  • 3
5 Comments
 
LVL 14

Expert Comment

by:BigBadWolf_000
ID: 24409071
Have you deleted a chart that is linked to data in your workbook. If yes, was the data on a different sheet from your chart?
0
 
LVL 14

Accepted Solution

by:
BigBadWolf_000 earned 500 total points
ID: 24409082
read this...and the solution is ????
http://support.microsoft.com/kb/931389
 
0
 
LVL 14

Expert Comment

by:BigBadWolf_000
ID: 24409088
The invalid references are linked somehow to the area of the sheet that contained the deleted chart and you cannot get rid of them even if you delete the entire rows.

What you need to do is this (where the sheet containing the error is named 'MySheet':

1. Backup workbook.
2. Rename the sheet that is causing the problem 'MySheetOld'
3. Create a new sheet 'MySheet' in your workbook.
4. Copy the rows in 'MySheetOld' down to but not including the area that
contained your deleted chart.
5. Paste into exactly the same rows in 'MySheet'.
6. Copy any rows below (but not including) the area in 'MySheetOld'.
7. Paste into exactly the same rows in 'MySheet'.
8. Change any names that point to 'MySheetOld' to point to 'MySheet'
9. On all other sheets in the workbook find and replace 'MySheetOld' in
formulae with 'MySheet'.
10. Save workbook.
11. Delete sheet 'MySheetOld'.
12. Error message should no longer appear.

You may have to play about with this a bit if you are not sure where tha deleted chart was. Usually you can work your way down a sheet, forcing arecalculate (grouping rows is a good and accurate way to do this - just progressively add rows to a group then close and open the group). When you get to the rows that the deleted chart was in the error message will start appearing.

Steps 6 & 7 may be problematic as you may not know where the corrupted
rows end.

Hope this helps
0
 
LVL 10

Expert Comment

by:Jon von der Heyden
ID: 24409517
I would especially suggest checking any names / named ranges for the error.  Perhaps data validation and conditional formatting to.  You may have something referncing a range that you deleted (not cleared) and therefore results in this error.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel 2013 Issues 11 45
Populating Cells in excel using a combo box in excel from an access table or query. 6 36
Excel VBA 30 38
NEED TRANSFER  DATA 59 20
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question