How do I change/delete external references in an Excel 2000 worksheet?

[mongous]

I have a worksheet that uses the 'Index' function to look up values from a 'calc' sheet. But some of the sheets are looking for the named range on another sheet (last year's version).

How can I get Excel to SHOW ME where/why it is calling for this other sheet? The named ranges were called the same last year, but that file isn't even in the same directory, and some of the sheets are finding the right data but others are looking for last years.(?)

Here is my actual formula:

=IF($U$15>$B$11,"",INDEX(feb10hrs,MATCH($A18,feb10rep,0))

Can anyone shine any light my way on this? Thanks.

 

[Dreamboat]

Insert-Name-Define and check out the ranges to which the named ranges refer... Anne Troy

http://www.TheOfficeExperts.com

 

[mongous]

Yeah, I tried that and it is showing the correct range...

 

[Dreamboat]

I assume all named ranges are in THIS workbook then?

Try this workaround:

Open the file.
Save to a new name (any name).
Hit Edit-Links and choose to Change the source.
Choose the Original file as your source.
Ok.
Save the file.
Now Save the file as the original file name, overwriting the original.
Links should be gone 'cause it's actually now linking to itself. Anne Troy

http://www.TheOfficeExperts.com

 

[mongous]

You are correct - they are all in this workbook. So I tried your workaround, and now all I get is N/A in all cells. It's like they are not even finding the range...

 

[Dreamboat]

Ok. Well, there's a findlinks.xla out there on the web somewhere, and a dellinks.xla from Microsoft... just use your friendly neighborhood search... Anne Troy

http://www.TheOfficeExperts.com

 

[mongous]

And this will help me to find and/or delete the bad links? Ok thanks! I will check it out.

 

[Dreamboat]

findlinks:

http://bmsltd.co.uk/MVP/MVPPage.asp

dellinks:

http://support.microsoft.com/?kbid=188449

Sorry, I was fairly busy earlier... Anne Troy

http://www.TheOfficeExperts.com

 

[mongous]

Not a problem! Thanks for all your help. I will try those utilities..