How do I find "invalid external reference"?

  • Thread starter Thread starter excel M
  • Start date Start date
E

excel M

I have modified a spreadsheet, and now am using "Save As" to save it.

When I save it I get the Microsoft Excel Alert (yellow triangle with
exclamation mark) with the message:
"Your formula contains an invalid reference to an external
worksheet".

I am not aware there is a formula in the worksheet that links to an
external worksheet. I guess I have inadvertently created one.

How do I find which formula this refers to?
Can I ask for a listing of all formula that link to external
worksheets?

I just don't know where to start looking for the problem.

Thanks,
Mike
 
Hello Mike

Try pressing the F5 (GoTo) button and choose Special, then
click the Formulas option.

All formulas will be selected - Now tab through them and
examine each formula nad look for one that looks something
like this =Personal.xls!$A$13

Perhaps others have a better answer

Peter
 
It happened (I think) when I changed the names of some worksheets that were
the targets of formulae, and then did a search/replace/all (if that's what
it is in English XL) to change the names in the corresponding formulae. XL
gave various "open file" windows that I just closed.

Exploring the formulae as suggested allowed me to redirect them to the
original file. Can't remember the details, as I didn't take notes. Moral:
changing worksheet names on the tabs is dangerous.

Regards
 
Back
Top