Strange error message

[Glen Mettler]

Using Excel 2002. When I save the workbook (has many formulas and macros) I
get this message:

"Your formula contains an invlaid external reference to a worksheet.
Verify that the pathe, workbook and range name or cell reference
are correct and try again." OK

I press OK and it saves. The workbook operates OK otherwise.

How can I find the "invalid external reference"

Glen

 

[Guest]

Glen, I hope this works...first time on here, looking for a formula for
"between"
anyhow... you have copied some formulas which have references to cells in
ther workbooks - they are linked
Find these formulas by
1.) TOOLS > Options>View> check the Formula
and OK
now when you save the workbook, copy down the name of the workbook it is
trying to link with (scroll over the name, (Ctrl+C) copy and paste it in a
"FIND" in the workbook. It should find the link then you will have to adjust
your formula if needed.

 

[Glen Mettler]

Thanks, wish I could help you on "Between" - I suppose you could write a
UDF with < and >

Glen

 

[Alex]

A few things come to mind for me:

1: Check the external link menu. It is under edit->Links. It will
the invalid references. I think in 2002, there is an option to "break
links" and copy the last snapshot of what the data *should* be into
wherever it was referenced. This will also tell you what to search for
when finding the links in your spreadsheet, assuming you want to fix
them instead of eliminate them.

2: If you can see an invalid link, but not find it, then you may want
to check the "names" in your work book. This menu is under
Insert->Name->Create. I don't know if there is a different name
collection for each sheet, so you may want to check the names on all of
your sheets. If you have an invalid reference here, doing a find won't
locate it.

3: Do a search for "#REF" in your worksheet. This may give you that
error so it's worth a shot.

Hope this helps!

Alex.

 

[Glen Mettler]

Alex,
I think the #REF was the culprit. Thanks!

Glen