Excel 97 - Find the Missing Link 1

[shebamurphy]

Hi -

I've created a spreadsheet which uses lookup formulas to change values in a worksheet depending on a value selected by a user from a list (I'm using Data Validation to restrict data entry).

The resultant cell values also need to be included in other spreadsheets, but for these spreadsheet I don't want users to be able to select a value. So I've copied and pasted the values to a new worksheet in the workbook which I want the fixed values to appear in.

When I open this new spreadsheet, though, one of two things happens: either a progress bar for "Link:" appears in the status bar, freezes at 100% and then Excel crashes; or the worksheet opens, but continues to try to reference the original worksheet (which will not be available to the end users I'm sending the sheet to).

In addition, when I delete the worksheet with fixed values from the workbook into which I inserted it and select Edit / Links, the original reference still appears.

What am I doing wrong?

 

[Hasit]

I am not exactly sure what you are trying to do, but if I understand it right, if you used linked sheets, for the values to be sent back and forth between sheets, particularly when you are doing a lookup, the spreadsheet you are getting the data from has to be open.

hasit

 

[Dreamboat]

Hey, hasit.

That's great!

Dreamboat

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[Hasit]

Thanks Dreamboat, although I hope its not sarcasm I hear in your writing. Or I would be really upset....

 

[Dreamboat]

Not a drop.

http://www.PiersonTech.com

techsupportgirl@home.com
Brainbench MVP for Microsoft Word

 

[Hasit]

I know, I was jus kid'n s-)

 

[shebamurphy]

I don't want to interrupt your chat, but:

The problem is that I don't want the link to the other sheet to be there, 'cos the people using the second spreadsheet won't have access to the first spreadsheet.

I've pasted into the second sheet as "values" specifically for this reason. But the link still seems to be there even though there are no formulas or external references in any cell in this sheet at all.

Any sensible answers please...?

 

[Hasit]

Shebamurphy, thanks for the star. Have a look at EDIT->LINKS and see whether the link is still there. If it is, then you will need to verify that you have deleted all the data that has been entered as a link.

 

[shebamurphy]

Hasit, I've checked Edit / Links already (mentioned in my first posting), and, yeah, the reference to the other worksheet is still there. But there shouldn't be any reference. I've manually looked at every cell where the reference was before I pasted the values, and have also done a Find on the whole workbook using the name of the linked file: in both cases there is no reference.

My guess is that the link is somewhere in the actual code for the sheet, but it isn't referenced in any actual cells (as far as I can see anyway). Is there any way I can get at this? Or would I be just as well to scrap it and try again?

 

[Hasit]

Hmmm. Can't think why the link is still listed as existing. I do not know what you mean by the code in the worksheet - unless you have macros or some such, but that should have no direct bearing on the link.

You might want to start again, but if the contents of the sheets are not secret, you can email me them, and I'll be happy to take a look.

hasitbakhda@yahoo.co.uk

 

[shebamurphy]

Hasit - No macros, but I've checked the VBA editor anyway. I'll email you the sheet separately. Thanks for the help! ;-)

 

[Hasit]

Shebamurphy. I have fixed your problem. You have what is known as a phantom link. This is uncommon, but does happen >:-<

I found an add-in called Findlink (which I have sent to you bye email).

All you need to do is add it in through TOOLS->ADD-IN and then through TOOLS->FIND LINK, follow the problems, and delete the links that are incorrect (particularly the &quot;PAF&quot; referenced links).

I did it to the file you sent me and after I finished, the entry disappears from the EDIT->LINKS dialogue box.

Good luck!

 

[Hasit]

Note to all.

Shebamurphy has confirmed that the problem has been fixed and that the FINDLINK add-in has worked. If anyone needs a copy, email me at hasitbakhda@yahoo.co.uk

 

[shebamurphy]

And I'd just like to say thanks very much to Hasit! ;-)