removing links

Topic

New Reply

In Excel 2000, I’m trying to create a new budget from last year’s, and somewhere in last year’s file there are links between months, so that some cell in, say, December reads its data from the November screen. Is there a way to copy the December page from this file to make the new year’s January screen without importing such internal links, so that the 2001 budget does not generate (upon opening) the update links, yes/no message? Put another way, how can I find and cut such links?

Thanks.

Reply | Quote

Replies

Will copying the December worksheet and doing a Paste Special/Values (then maybe a Paste Special/Format) do what you want?

Reply | Quote

After you copy your December file and make it to January or whatever, Select Edit, Links. The Links dialog box should appear. Click on the link (in EXCEL97there is a liottle bar below which shows the name of the file or sheet linkage that you selected). Then Click the Change Source button and change the link to the active file or sheet that you now want to link to. If that does not work, then.Select Insert, Name, Define. Scroll down the list in the Define Name dialog box and examine the “Refers to” box. Delete names that refer to another workbook or that contain an erroneous reference (such as #REF!). This is the most common cause of “phantom
links.”

Reply | Quote

I found a small utilty, FindLink that you install which makes it easey to find most links.
I have a copy of the zip file is someone would advise me where to send it so it is available to all.

Reply | Quote

Is there a web site for this? It sounds good.

Reply | Quote

Edited by gwhitfield on 01/01/15 23:15.

*** I’ve added tags to the web reference- but it’s put my sig in place of Gene’s ***

The only item that I’ve seen while surfing Excel links that fits this description is the one from MacroSystems which is shareware. See: http://www.add-ins.com/linkfind.htm%5B/url%5D

Reply | Quote

Thanks Gene- looks good. But I don’t know why it replaced your sig with mine- sorry.

Reply | Quote

I’ve seen that happen a few times — and even to HRH.

HELP, MUMMY! The signature switcher gremlin is alive.

Reply | Quote

Gene-

Is it shareware? I’ve looked at the link and it looks as if you have to purchase a VB suite of programs to get it.

Reply | Quote

Geoff, it’s part of a package of Excel utilities that are for sale. I also have the Bill Manville file which I’d forgotten about. The link you’ve already included in your post is accurate and should be sufficient. I double checked the download and it looks to be still valid.

Reply | Quote

FWIW, found the same here: BMS – Stephen Bullen

and there’s a number of other cool things, all of which appear to be freeware, with a specific prohibition against charging a fee.

Edited Mar 13 04 to update link

Reply | Quote

Hi Geoff,
Sorry, the only information that I can find is that it is written by Bill Manville. I picked this utility up from a recommendation in a newsletter some time ago, but cannot recall which one. I have a number of users throughout the company using this utility, and they all love it.
I still have the original zip file, so I can send this to you in an email if you give me your address, as it is only 10.8KB.

Reply | Quote

There is a delinker you can get from the Microsoft website. Just search for dellinks.xla.

Reply | Quote

cpod,

Thanks for that. Here’s the link

Reply | Quote

Paul,

That’s OK. I found it here

Reply | Quote

How about attaching it to a response to this message Board? I have run into this problem many times and would love to have it in my tool box.
Thanksstollma@juno.com

Reply | Quote

Hi,

You can download it from the accounting advisors webite.

I’d rather point people at that site than post the actual sheet here.

There’s also a Microsoft solution for the same problem

Reply | Quote

Here is the address to download

http://www.cpearson.com/excel/xltools.htm

Reply | Quote

Hello,

I different approach is to use the INDIRECT function – no more “internal links” !

Regards

–alex

Reply | Quote

The indirect function is a new one to me … can you give me another scenario where it is useful?

Reply | Quote

You can build functions and references in text format and then extract the value of the “real” cells. Here’s part of one I did a few months back, where the sheet names were in column A of a summary sheet:

Sheet name      formula                       returns
SAN DIEGO      =INDIRECT("'"&a1&"'!E22")     $xx.xx(the value of cell 'SAN DIEGO'!E22)
LOS ANGELES    =INDIRECT("'"&a2&"'!E22")     $yy.yy(the value of cell 'LOS ANGELES'!E22)
NOR CAL        =INDIRECT("'"&a3&"'!E22")     $zz.zz(the value of cell 'NOR CAL'!E22)

Does this help?

Reply | Quote

Yah! thanks …. now I’m wondering where I can use it.*hmmmmm*

Reply | Quote

One caution on using INDIRECT…

If the reference is to an external workbook, the workbook MUST be open or INDIRECT will return #REF.

Reply | Quote

I had what seems to be a similar problem. Running in Excel 97 but I think it will apply to 2000.

I had 2 copies of the same workbook (don’t ask how they got out of sync). Ver A had some updated values which Ver B didn’t have but B had a new sheet which I wanted to get into A.

I didn’t want to copy the values from A to B bcs I might mess up and not get them all. So I decided to copy the sheet from B to A. I renamed B so I could have both workbooks open at the same time. Then I did a copy of the sheet in B to A. I then removed B (had it on floppy just in case).

None of the formulas in the extra sheet had any links but A had a link to B. Nothing in a cell of Ver A referred to B but there it was – in the set of links – a reference to B.

Looked at help – no help, almost.

One of the things you can do in the Edit | Links is to change the source of a link. So I selected the link to B, clicked change source, navigated to the filename for A (ie, change the source to point to itself), clicked OK, and voila – the link to B was gone.

Hope this helps. If anyone tries this out in 2K, let us know how it works.

Fred

Reply | Quote