You are here:

Excel/invalid references

Advertisement


Question
QUESTION: "Hi Aidan

I worbkbook that I have been using for many years has suddenly started to display this messasge when I try to save it:

“A formula in this worksheet contains one or more invalid references…”

The workbook is quite large and complex.  The information that informs all of the functions and charts is placed in one single worksheet.  This worksheet is changed every new academic year to reflect new students who arrive at the shcool.

This is the only change.  Structurally e.g column heading, formulas...nothing else changes.

I have done some reading.  It seems that this is quite a difficult problem.

Do you know of any code that might identify these invalid references?

Thanks in advance.

Chris Mitchell"

ANSWER: No problem - this page http://excelribbon.tips.net/T008664_Tracking_Down_Invalid_References.html explains how to solve it and gives an example macro.

---------- FOLLOW-UP ----------

QUESTION: Hi Aiden

Thanks you this prompt response.  I have actually tried this macro it made excel freexe up.

YOu might be able to comment on this. I created a button and called it "Save"  The VBA assigned to it is:

Sub SAVEWORKBOOK()
'
' SAVEWORKBOOK Macro
'

'
   ActiveWorkbook.Save
End Sub

When I press this button the workbook saves without errors.  When I use fole/save or ctrl/s  I receive the message about invalid references.  Have you any idea why this might be the case?

Chris Mitchell

Answer
I would GUESS that the file is saving from the macro, but with errors, just not reporting them.  As to freezing, you've said the file is complex, so I would GUESS it would take some time to run - setting calculation to manual may help - I'd also check if you have any external references that these are valid.  Happy to look at the file if you think it may help - my email is aidan.heritage@virgin.net
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.