If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I find "invalid external reference"?
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 --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
How do I find "invalid external reference"?
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 -----Original Message----- 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 --- Message posted from http://www.ExcelForum.com/ . |
#3
|
|||
|
|||
How do I find "invalid external reference"?
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 "Peter Atherton" a écrit dans le message de news: ... 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 -----Original Message----- 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 --- Message posted from http://www.ExcelForum.com/ . |
#4
|
|||
|
|||
How do I find "invalid external reference"?
|
Thread Tools | |
Display Modes | |
|
|