dropping a reference without losing the data?

[sabotage619]

Hi all,
I have cells of text referenced from another file. I'd like to keep the text but drop the reference. There are thousands of cells and I'd like to avoid the hours of data entry. Any advice?

[FDibbins]

Hi, welcome to the forum

If I understand you correctly, copy the entire range then paste values over itself

[sabotage619]

ahh, good answer, except it is on hundreds of sheets. Is there any work around to avoid the cut and paste? I thought maybe there was a way to "mirror" or copy references in one process.

[FDibbins]

Are they all basically the same?

If so, click on the 1st sheet, hold shift and click on the last sheet (this will group them). Then do the highlight/copy/paste values thing

[sabotage619]

for example

A1:Item number B1:reference text
A2:Item desc B2:reference text
A3:Cost per item B3:reference text


so about 16 cells of referenced text times hundreds of worksheets. On each worksheet A1-A3 are the same, but B1-B3 (the references) change.
This is probably better tackled using Access but I'm stuck using excel.

[FDibbins]

Did you try the sheet grouping method I suggested?

[sabotage619]

Thanks for your help. Here is the solution I was looking for, for future reference.


Break a link to a source

Important When you break a link to a source, all formulas that use the source are converted to their current value. For example, the link =SUM([Budget.xls]Annual!C10:C25) would be converted to =45. Because this action cannot be undone, you may want to save a version of the file.

1.On the Data tab, in the Connections group, click Edit Links.


2.In the Source list, click the link that you want to break.

To select multiple linked objects, hold down CTRL and click each linked object.

To select all links, press CTRL+A.


3.Click Break Link.


4.If the link used a defined name, the name is not automatically removed. You may want to delete the name as well.

How to delete a name

a.On the Formulas tab, in the Named Cells group, click Name Manager.


b.In the Name column, click the name that you want to delete, and then click Delete.



5.If you use an external data range, a parameter of a query may also use data from another workbook. You may want to check for and remove any of these types of links.