Excel Hell (2000 version)

[Sylvor]

I love using little rhymes in my titles.

Anyway,

I have a huge Excel workbook, the workbook has cells which have data linked from other workbooks - my problem is that these other workbooks no longer exist... I can copy the cells and "paste special" the values in, which is great. But I can't actually find the darn cells!!!

I have tried using "Find" and searching for the workbook names in formulas, but for some reason it will NOT find anything, I AM typing everything correctly (Exactly as is shown in Edit> Links)

Somebody please shed some healing light.

"Gaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaah" - Me, 2002


------------------
Sly :)

"Helping everyday people with everyday problems, everyday!"

"The ability to quote is a serviceable substitute for wit"

[Paul Komski]

Your post is a little confusing. If the linked workbooks don't exist then there can be no cells to find in a non-existent workbook. However if you are able to PasteSpecial values then you should be able to get the path to the other sheet or workbook from the formula. Alternatively they are not linked and only contain values. Have you tried searching for the other (non-existent) workbooks by searching for *.xls and get a list of all the workbooks on your pc?


------------------
Take nice care of yourselves - Paul
"People in glasshouses ..... shouldn't undress during daylight!"

[Sylvor]

Paul,

Thanks for the reply - I guess the post is confusing, but it's a strange one y'see - I have an Excel workbook which has links to other "old" workbooks, the links dialog says that these linked books are from the A:\... - they obviously existed at one point, the data is still in the cells but just hasn't updated for ages - all I want to do is remove the links so that the data (values) stay as they are

(This will stop the darn workbook prompting me everytime I open it!!)

Now for the weird part... when I try and use the find feature, I cannot actually find any reference to these linked books. (I type "a:\" or similar yet nothing is returned!)

Confused

------------------
Sly :)

"Helping everyday people with everyday problems, everyday!"

"The ability to quote is a serviceable substitute for wit"

[Paul Komski]

Sylvor
My best guess as to what is going on is that the data was originally linked to another workbook by the data having been copied and pasted(special) as embedded data rather than as linked data. Since the original workbook is non-existent it is impossible to update the data and so it remains as it is, even though the link to the original source remains. The embedded link would be updatable if the original was still in existence or had been moved and could be found (which would be done by Edit<>Links).

I would make a copy of your sheet(s) and then select all and copy and then paste special (as values) and if all goes to plan you can then delete the original worksheet with the embedded data. Just ensure that it is values that you want and not formulae (if the original involved cross calculations). If that was the case then first paste special (as formulae) into your new copied sheet and only thereafter copy/paste the values as necessary. If the data is important then you could just make a copy of the .xls file and mess around with the copy of the workbook till you are happy with the results.

Hope this helps.

------------------
Take nice care of yourselves - Paul
"People in glasshouses ..... shouldn't undress during daylight!"