I have a sheet that persistently reports, "External reference is not valid"

I can't find the invalid reference. How can I find it?

[Excel for Windows 95 Version 7.0]

Thanks for any suggestion.

I'd use Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

But I _think_ that it requires xl97 or newer.

If you try it and it doesn't work, maybe you can search through formulas for:
..xls
[
#ref!

Maybe that'll help you find a formula that is broken.


Mason C wrote:

I have a sheet that persistently reports, "External reference is not valid"

I can't find the invalid reference. How can I find it?

[Excel for Windows 95 Version 7.0]

Thanks for any suggestion.

--

Dave Peterson

On Sat, 10 Oct 2009 17:43:54 -0500, Dave Peterson
wrote:

I'd use Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

But I _think_ that it requires xl97 or newer.

If you try it and it doesn't work, maybe you can search through formulas for:
.xls
[
#ref!

Maybe that'll help you find a formula that is broken.

Dave:

FindLink doesn't work in my Excel version. (says, "not a valid add-in")
The searches for xls, [ , #ref! find nothing.
The sheet is small enough that I have scanned it for any reference: nothing.

Thanks for the suggestions anyway.

Mason Clark


Mason C wrote:

I have a sheet that persistently reports, "External reference is not valid"

I can't find the invalid reference. How can I find it?

[Excel for Windows 95 Version 7.0]

Thanks for any suggestion.

Can you find someone with xl97 or greater and ask them to see if they can use
the Bill's program and look at your file?

Mason C wrote:

On Sat, 10 Oct 2009 17:43:54 -0500, Dave Peterson
wrote:

I'd use Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

But I _think_ that it requires xl97 or newer.

If you try it and it doesn't work, maybe you can search through formulas for:
.xls
[
#ref!

Maybe that'll help you find a formula that is broken.

Dave:

FindLink doesn't work in my Excel version. (says, "not a valid add-in")
The searches for xls, [ , #ref! find nothing.
The sheet is small enough that I have scanned it for any reference: nothing.

Thanks for the suggestions anyway.

Mason Clark


Mason C wrote:

I have a sheet that persistently reports, "External reference is not valid"

I can't find the invalid reference. How can I find it?

[Excel for Windows 95 Version 7.0]

Thanks for any suggestion.

--

Dave Peterson

On Sun, 11 Oct 2009 07:19:46 -0500, Dave Peterson
wrote:

Can you find someone with xl97 or greater and ask them to see if they can use
the Bill's program and look at your file?

Don't know anyone I'd bother to do that (I would scare them :-).

I replaced all the 3 or 4 " .xls " and " #ref! " in the file
(binary search/replace) with no effect -

sheet seems undamaged but still gives " External reference not valid" warning.

I can survive annoyed with the extra mouse clicks. Thanks anyway.

Mason Clark


Mason C wrote:

On Sat, 10 Oct 2009 17:43:54 -0500, Dave Peterson
wrote:

I'd use Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

But I _think_ that it requires xl97 or newer.

If you try it and it doesn't work, maybe you can search through formulas for:
.xls
[
#ref!

Maybe that'll help you find a formula that is broken.

Dave:

FindLink doesn't work in my Excel version. (says, "not a valid add-in")
The searches for xls, [ , #ref! find nothing.
The sheet is small enough that I have scanned it for any reference: nothing.

Thanks for the suggestions anyway.

Mason Clark


Mason C wrote:

I have a sheet that persistently reports, "External reference is not valid"

I can't find the invalid reference. How can I find it?

[Excel for Windows 95 Version 7.0]

Thanks for any suggestion.

If you want to zip the file and send me a copy (privately--not to the
newsgroup), you can.

If it has confidential data in it, then maybe you can make a copy, remove that
data and I'll see if I can find that link using Bill's program and report back
to you.

Mason C wrote:

On Sun, 11 Oct 2009 07:19:46 -0500, Dave Peterson
wrote:

Can you find someone with xl97 or greater and ask them to see if they can use
the Bill's program and look at your file?

Don't know anyone I'd bother to do that (I would scare them :-).

I replaced all the 3 or 4 " .xls " and " #ref! " in the file
(binary search/replace) with no effect -

sheet seems undamaged but still gives " External reference not valid" warning.

I can survive annoyed with the extra mouse clicks. Thanks anyway.

Mason Clark


Mason C wrote:

On Sat, 10 Oct 2009 17:43:54 -0500, Dave Peterson
wrote:

I'd use Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

But I _think_ that it requires xl97 or newer.

If you try it and it doesn't work, maybe you can search through formulas for:
.xls
[
#ref!

Maybe that'll help you find a formula that is broken.

Dave:

FindLink doesn't work in my Excel version. (says, "not a valid add-in")
The searches for xls, [ , #ref! find nothing.
The sheet is small enough that I have scanned it for any reference: nothing.

Thanks for the suggestions anyway.

Mason Clark


Mason C wrote:

I have a sheet that persistently reports, "External reference is not valid"

I can't find the invalid reference. How can I find it?

[Excel for Windows 95 Version 7.0]

Thanks for any suggestion.

--

Dave Peterson

On Mon, 12 Oct 2009 15:59:53 -0500, Dave Peterson
wrote:

If you want to zip the file and send me a copy (privately--not to the
newsgroup), you can.

Dave,

In response to your too kind offer, I stripped the workbook down to
the offending sheet and stripped that sheet to a minimum.

The offending bit that's left contains:

column A of dates
column C with the formula: =IF(A1=Today(),99,A1)
and a graph (originally quite complicated)

The "99" serves to mark Today on the graph.
A very nice touch for my purpose if I do say so but....

It makes any entry anywhere cause an "External reference not valid"

What's wrong with my clever trick?

The sheet can be seen at http://frontal-lobe.info/misc/clarkworkbook.xls

The mystery evolves on a dark and stormy night in northern California.

Thanks again, Dave,

Mason


Mason C wrote:
Mason Clark


The original post:

I have a sheet that persistently reports, "External reference is not valid"

I can't find the invalid reference. How can I find it?

[Excel for Windows 95 Version 7.0]

Thanks for any suggestion.

This message may not be related to anything in your worksheets.

I opened the file and didn't see any warning.

So I went into the VBE (alt-f11) and with the workbook's project selected,
clicked on:

Tools|References

You have a reference to OLE Automation.

I removed that reference, saved and reopened your workbook. I didn't get any
error messages.

But the bad news is that I don't remember how to do the same thing in xl95. I
looked at one of my old reference (no pun intended!) books and it didn't offer
any help.

I uploaded the file to:
http://www.filedropper.com/clarkworkbook2

If that works ok, maybe it's worth looking in xl95's help for references????

Hope it helped...

Mason C wrote:

On Mon, 12 Oct 2009 15:59:53 -0500, Dave Peterson
wrote:

If you want to zip the file and send me a copy (privately--not to the
newsgroup), you can.

Dave,

In response to your too kind offer, I stripped the workbook down to
the offending sheet and stripped that sheet to a minimum.

The offending bit that's left contains:

column A of dates
column C with the formula: =IF(A1=Today(),99,A1)
and a graph (originally quite complicated)

The "99" serves to mark Today on the graph.
A very nice touch for my purpose if I do say so but....

It makes any entry anywhere cause an "External reference not valid"

What's wrong with my clever trick?

The sheet can be seen at http://frontal-lobe.info/misc/clarkworkbook.xls

The mystery evolves on a dark and stormy night in northern California.

Thanks again, Dave,

Mason


Mason C wrote:
Mason Clark


The original post:

I have a sheet that persistently reports, "External reference is not valid"

I can't find the invalid reference. How can I find it?

[Excel for Windows 95 Version 7.0]

Thanks for any suggestion.

--

Dave Peterson

Dave,

If I create a new, blank workbook on an empty screen it seems ok.

If I then load my "real" workbook along side the new, the blank new
one shows "External reference not valid" upon any attempt to enter anything.

That workbook is normally loaded automatically along with two others via
a workspace.xlw and the other two are well-behaved.

This is too weird for my brain.

At this point I will abandon the repair project and live with the need to
clear "External reference not valid" whenever I enter new data.

Fortunately, at this point I only need to do that once a day so it is
tolerable.

Thanks for all your efforts to help.

Mason Clark


On Tue, 13 Oct 2009 08:47:18 -0500, Dave Peterson
wrote:

This message may not be related to anything in your worksheets.

I opened the file and didn't see any warning.

So I went into the VBE (alt-f11) and with the workbook's project selected,
clicked on:

Tools|References

You have a reference to OLE Automation.

I removed that reference, saved and reopened your workbook. I didn't get any
error messages.

But the bad news is that I don't remember how to do the same thing in xl95. I
looked at one of my old reference (no pun intended!) books and it didn't offer
any help.

I uploaded the file to:
http://www.filedropper.com/clarkworkbook2

If that works ok, maybe it's worth looking in xl95's help for references????

Hope it helped...

Mason C wrote:

On Mon, 12 Oct 2009 15:59:53 -0500, Dave Peterson
wrote:

If you want to zip the file and send me a copy (privately--not to the
newsgroup), you can.

Dave,

In response to your too kind offer, I stripped the workbook down to
the offending sheet and stripped that sheet to a minimum.

The offending bit that's left contains:

column A of dates
column C with the formula: =IF(A1=Today(),99,A1)
and a graph (originally quite complicated)

The "99" serves to mark Today on the graph.
A very nice touch for my purpose if I do say so but....

It makes any entry anywhere cause an "External reference not valid"

What's wrong with my clever trick?

The sheet can be seen at http://frontal-lobe.info/misc/clarkworkbook.xls

The mystery evolves on a dark and stormy night in northern California.

Thanks again, Dave,

Mason


Mason C wrote:
Mason Clark


The original post:

I have a sheet that persistently reports, "External reference is not valid"

I can't find the invalid reference. How can I find it?

[Excel for Windows 95 Version 7.0]

Thanks for any suggestion.

If you ever find a good solution <vbg, please post back.

Mason C wrote:

Dave,

If I create a new, blank workbook on an empty screen it seems ok.

If I then load my "real" workbook along side the new, the blank new
one shows "External reference not valid" upon any attempt to enter anything.

That workbook is normally loaded automatically along with two others via
a workspace.xlw and the other two are well-behaved.

This is too weird for my brain.

At this point I will abandon the repair project and live with the need to
clear "External reference not valid" whenever I enter new data.

Fortunately, at this point I only need to do that once a day so it is
tolerable.

Thanks for all your efforts to help.

Mason Clark

On Tue, 13 Oct 2009 08:47:18 -0500, Dave Peterson
wrote:

This message may not be related to anything in your worksheets.

I opened the file and didn't see any warning.

So I went into the VBE (alt-f11) and with the workbook's project selected,
clicked on:

Tools|References

You have a reference to OLE Automation.

I removed that reference, saved and reopened your workbook. I didn't get any
error messages.

But the bad news is that I don't remember how to do the same thing in xl95. I
looked at one of my old reference (no pun intended!) books and it didn't offer
any help.

I uploaded the file to:
http://www.filedropper.com/clarkworkbook2

If that works ok, maybe it's worth looking in xl95's help for references????

Hope it helped...

Mason C wrote:

On Mon, 12 Oct 2009 15:59:53 -0500, Dave Peterson
wrote:

If you want to zip the file and send me a copy (privately--not to the
newsgroup), you can.

Dave,

In response to your too kind offer, I stripped the workbook down to
the offending sheet and stripped that sheet to a minimum.

The offending bit that's left contains:

column A of dates
column C with the formula: =IF(A1=Today(),99,A1)
and a graph (originally quite complicated)

The "99" serves to mark Today on the graph.
A very nice touch for my purpose if I do say so but....

It makes any entry anywhere cause an "External reference not valid"

What's wrong with my clever trick?

The sheet can be seen at http://frontal-lobe.info/misc/clarkworkbook.xls

The mystery evolves on a dark and stormy night in northern California.

Thanks again, Dave,

Mason


Mason C wrote:
Mason Clark


The original post:

I have a sheet that persistently reports, "External reference is not valid"

I can't find the invalid reference. How can I find it?

[Excel for Windows 95 Version 7.0]

Thanks for any suggestion.

--

Dave Peterson

On Tue, 13 Oct 2009 18:19:36 -0500, Dave Peterson
wrote:

If you ever find a good solution <vbg, please post back.

Dave,

Simple. I copy-pasted the entire sheet to a new worksheet on the same
workbook.

(copy either as A1 to AC400 OR by highlighting the entire worksheet)

The new sheet does not show "External reference not valid."

The old sheet, apparently identical, still shows that error whenever
it is touched.

???? Wonders of computers. Oh, well, all's well.....

Mason

Mason C wrote:

Dave,

If I create a new, blank workbook on an empty screen it seems ok.

If I then load my "real" workbook along side the new, the blank new
one shows "External reference not valid" upon any attempt to enter anything.

That workbook is normally loaded automatically along with two others via
a workspace.xlw and the other two are well-behaved.

This is too weird for my brain.

At this point I will abandon the repair project and live with the need to
clear "External reference not valid" whenever I enter new data.

Fortunately, at this point I only need to do that once a day so it is
tolerable.

Thanks for all your efforts to help.

Mason Clark

On Tue, 13 Oct 2009 08:47:18 -0500, Dave Peterson
wrote:

This message may not be related to anything in your worksheets.

I opened the file and didn't see any warning.

So I went into the VBE (alt-f11) and with the workbook's project selected,
clicked on:

Tools|References

You have a reference to OLE Automation.

I removed that reference, saved and reopened your workbook. I didn't get any
error messages.

But the bad news is that I don't remember how to do the same thing in xl95. I
looked at one of my old reference (no pun intended!) books and it didn't offer
any help.

I uploaded the file to:
http://www.filedropper.com/clarkworkbook2

If that works ok, maybe it's worth looking in xl95's help for references????

Hope it helped...

Mason C wrote:

On Mon, 12 Oct 2009 15:59:53 -0500, Dave Peterson
wrote:

If you want to zip the file and send me a copy (privately--not to the
newsgroup), you can.

Dave,

In response to your too kind offer, I stripped the workbook down to
the offending sheet and stripped that sheet to a minimum.

The offending bit that's left contains:

column A of dates
column C with the formula: =IF(A1=Today(),99,A1)
and a graph (originally quite complicated)

The "99" serves to mark Today on the graph.
A very nice touch for my purpose if I do say so but....

It makes any entry anywhere cause an "External reference not valid"

What's wrong with my clever trick?

The sheet can be seen at http://frontal-lobe.info/misc/clarkworkbook.xls

The mystery evolves on a dark and stormy night in northern California.

Thanks again, Dave,

Mason


Mason C wrote:
Mason Clark


The original post:

I have a sheet that persistently reports, "External reference is not valid"

I can't find the invalid reference. How can I find it?

[Excel for Windows 95 Version 7.0]

Thanks for any suggestion.

I guess my definition for good isn't the same as yours <vbg.

But glad you have it working.

Mason C wrote:

On Tue, 13 Oct 2009 18:19:36 -0500, Dave Peterson
wrote:

If you ever find a good solution <vbg, please post back.

Dave,

Simple. I copy-pasted the entire sheet to a new worksheet on the same
workbook.

(copy either as A1 to AC400 OR by highlighting the entire worksheet)

The new sheet does not show "External reference not valid."

The old sheet, apparently identical, still shows that error whenever
it is touched.

???? Wonders of computers. Oh, well, all's well.....

Mason

Mason C wrote:

Dave,

If I create a new, blank workbook on an empty screen it seems ok.

If I then load my "real" workbook along side the new, the blank new
one shows "External reference not valid" upon any attempt to enter anything.

That workbook is normally loaded automatically along with two others via
a workspace.xlw and the other two are well-behaved.

This is too weird for my brain.

At this point I will abandon the repair project and live with the need to
clear "External reference not valid" whenever I enter new data.

Fortunately, at this point I only need to do that once a day so it is
tolerable.

Thanks for all your efforts to help.

Mason Clark

On Tue, 13 Oct 2009 08:47:18 -0500, Dave Peterson
wrote:

This message may not be related to anything in your worksheets.

I opened the file and didn't see any warning.

So I went into the VBE (alt-f11) and with the workbook's project selected,
clicked on:

Tools|References

You have a reference to OLE Automation.

I removed that reference, saved and reopened your workbook. I didn't get any
error messages.

But the bad news is that I don't remember how to do the same thing in xl95. I
looked at one of my old reference (no pun intended!) books and it didn't offer
any help.

I uploaded the file to:
http://www.filedropper.com/clarkworkbook2

If that works ok, maybe it's worth looking in xl95's help for references????

Hope it helped...

Mason C wrote:

On Mon, 12 Oct 2009 15:59:53 -0500, Dave Peterson
wrote:

If you want to zip the file and send me a copy (privately--not to the
newsgroup), you can.

Dave,

In response to your too kind offer, I stripped the workbook down to
the offending sheet and stripped that sheet to a minimum.

The offending bit that's left contains:

column A of dates
column C with the formula: =IF(A1=Today(),99,A1)
and a graph (originally quite complicated)

The "99" serves to mark Today on the graph.
A very nice touch for my purpose if I do say so but....

It makes any entry anywhere cause an "External reference not valid"

What's wrong with my clever trick?

The sheet can be seen at http://frontal-lobe.info/misc/clarkworkbook.xls

The mystery evolves on a dark and stormy night in northern California.

Thanks again, Dave,

Mason


Mason C wrote:
Mason Clark


The original post:

I have a sheet that persistently reports, "External reference is not valid"

I can't find the invalid reference. How can I find it?

[Excel for Windows 95 Version 7.0]

Thanks for any suggestion.

--

Dave Peterson

The original post:

I have a sheet that persistently reports, "External reference is not valid"

I can't find the invalid reference. How can I find it?

[Excel for Windows 95 Version 7.0]

Thanks for any suggestion.

I solved my "reference not valid" problems by copying all seven sheets (using
upper left button to highlight the entire sheet ) from the troubled workbook to
a new workbook. The lost macros did not copy. It took a little work to erase
the old workbook filename from the chart data series references. And recreate
the macro I *did* want. All's clear now, thanks.