Changing multiple reference links from one workbook to another

[jlane]

Hi all,

I'm hoping someone here can assist me.

I'm working with a series of linked workbooks that are
undergoing structural changes. These workbooks have a
series of linked columns, and all of the reference links
have to be changed to accommodate these changes.

For example: I currently have a column in Workbook1 that
contains the following set of reference formulas:

='[Workbook2]Sheet1'!A1 ... ='[Workbook2]Sheet1'!A10

With the current changes, all of the A references need to
be changed to B.

Does anyone know of any way to do this without having to
either change each cell individually, or having to change
the top cell and then drag down to autofill? There are a
large number of these columns, and what I want to be able
to do is select the whole column and find some way to
change the references through the whole column in one
step.... sort of like a find & replace within the formula.

Is this possible? and if so, how?

 

[Andy B]

Hi

Try using Find/Replace. Select your range and hit Ctrl H
In the find box type Sheet1'!A (if that is enough to
distinguish it)
in the replace box type Sheet1'!B

Andy.