How to . . . remove Excel 1997-2003 WorkBook name from formulas copied to a New Sheet in Excel 2010

[JayDee]

Hi everyone

I am a retired Guy, trying to update an old WorkBook (Excel 1997-2003 - with many Sheets) into Excel 2010 format.

I have copied a sheet over, retaining the Formulas, Formatting and Column widths !

The problem is that the individual cell Formulas have carried over the reference to the old WorkBook cell. The formula looks very clumsy (and is difficult to read) and is very time-consuming to correct so it refers only the New WorkBook

Does anyone know how to copy over Formulas etc without the old WorkBook sheet references ?

 

[Hui]

JayDee

Firstly, Welcome to the Chandoo.org Forums

Goto Data, Edit Links
Select the Workbook that is linked to and link the file to itself

 

[JayDee]

Sorry to take so long before replying, but my mind has been consumed with a Business Model I was updating !
I have tried your suggestion, but not really with any success.

I selected the particular 'incorrect' reference (the only 'incorrect' reference out of many references in the Workbook) and tried to 'Change Source' of the incorrect reference to the Current Working sheet. However, this did not eliminate the incorrect source.

Separately, in the case of another (smaller ) workbook with a similar problem, I DID change the incorrect source by pointing the Source to the same sheet I was working in, as there was only one 'old' linked Spreadsheet reference.
On this attempt, it did eliminate the references to the 'old' Workbook from which I had copied Formulas, so it worked as hoped.

Breaking the Link for an incorrect reference does not appear to do the job either.

Anyway, I have attached a sheet showing a 'One-Note' Pic of the 'Edit Links' which shows the incorrect reference to an old (and deleted) sheet. If I could somehow trace the Cell with the incorrect reference , I could simply change the Cell(s). Might that be possible (using the wrong Link details) in the 'FIND' function ?

is there any other way of eliminating this problem, or am I just not tackling it correctly ? Thank you for your patience !