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.