Your formula contains an invalid external reference to a worksheet

  • Thread starter Thread starter peter
  • Start date Start date
P

peter

I'm sure this has been asked before, but I've just wasted a whole day
trying to find a solution and got nowhere!

I have a complex spreadsheet which I inherited from my predecessor.
It contains many sheets, plus macros, chart objects, text boxes, auto
shapes, etc. When I try to close the spreadsheet it objects "Your
formula contains an invalid external reference to a worksheet".

Try as I might, I just can't pin this down. I've tried the obvious,
such as searching formulae for '[', looking in named ranges and
asigned macro names to no avail. In desperation I tried deleting
worksheets one at a time to see if I could locate the problem. Sure
enough, I reached a specific worksheet, and when that was gone the
problem went away. But when I examined that worksheet, I found no
links. So I tried clearing all the cells, deleting all the objects,
etc, until I reached an apparently blank sheet, and still it claimed
to have an invalid link. Worse, when I restarted with a fresh copy of
the workbook, and just deleted the suspicious sheet, it STILL claimed
to have these links.

I'm at my wits end here - it seems like I'm chasing a will o' the wisp
that jumps around the workbook at random. I'm on a corporate network
where downloading extra software is a no-no, so I can't try any of the
utilities I've seen mentioned. Excel must know where these links are
- else how could it generate the message - but I just can't find out!

I'm going home now - any help will be gratefully acknowledged
tomorrow.

Regards

Peter
 
I'd use Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm
I'm sure this has been asked before, but I've just wasted a whole day
trying to find a solution and got nowhere!

I have a complex spreadsheet which I inherited from my predecessor.
It contains many sheets, plus macros, chart objects, text boxes, auto
shapes, etc. When I try to close the spreadsheet it objects "Your
formula contains an invalid external reference to a worksheet".

Try as I might, I just can't pin this down. I've tried the obvious,
such as searching formulae for '[', looking in named ranges and
asigned macro names to no avail. In desperation I tried deleting
worksheets one at a time to see if I could locate the problem. Sure
enough, I reached a specific worksheet, and when that was gone the
problem went away. But when I examined that worksheet, I found no
links. So I tried clearing all the cells, deleting all the objects,
etc, until I reached an apparently blank sheet, and still it claimed
to have an invalid link. Worse, when I restarted with a fresh copy of
the workbook, and just deleted the suspicious sheet, it STILL claimed
to have these links.

I'm at my wits end here - it seems like I'm chasing a will o' the wisp
that jumps around the workbook at random. I'm on a corporate network
where downloading extra software is a no-no, so I can't try any of the
utilities I've seen mentioned. Excel must know where these links are
- else how could it generate the message - but I just can't find out!

I'm going home now - any help will be gratefully acknowledged
tomorrow.

Regards

Peter
Click to expand...
 
Thanks, but unfortunately I am on a corporate network where I am not
able to download or install third party applications. Any other
ideas?

Peter
 
Ask for help from your IT staff. Suggest Bill Manville's addin. Maybe they'll
make an exception.
 
Back
Top