changing the address of data in a cell

[Darius]

I have several sheets in a file and cells in the sheets read data from
different other excel files. In some cells in each sheet (hard to find one by
one) the address is for example:

='D:\Gaines\Sourcefiles\Wet\Result\[2005.xls]AA340501'!E6

I want to change the address to:

='D:\Gaines\Sourcefiles\Wet\Result\[1995.xls]AB623501'!E6

I have to add that I have data in different cells not just E6. Instead of
cell by cell how can change the address (like above example) for the whole
sheet?

Appreciate your help

 

[Pete_UK]

Highlight the whole sheet by clicking on the box which intersects the
row and column labels, and then do Find and replace (CTRL-H):

Find what: [2005.xls]AA340
Replace with: [1995.xls]AB623
click replace all

Hope this helps.

Pete

 

[Darius]

Thanks

How do I do it for the whole file if I have mutiple sheets in it and do not
want to do it sheet by sheet?

Highlight the whole sheet by clicking on the box which intersects the
row and column labels, and then do Find and replace (CTRL-H):

Find what: [2005.xls]AA340
Replace with: [1995.xls]AB623
click replace all

Hope this helps.

Pete

I have several sheets in a file and cells in the sheets read data from
different other excel files. In some cells in each sheet (hard to find one by
one) the address is for example:

='D:\Gaines\Sourcefiles\Wet\Result\[2005.xls]AA340501'!E6

I want to change the address to:

='D:\Gaines\Sourcefiles\Wet\Result\[1995.xls]AB623501'!E6

I have to add that I have data in different cells not just E6. Instead of
cell by cell how can change the address (like above example) for the whole
sheet?

Appreciate your help

 

[Gord Dibben]

In find/replace options select Workbook.


Gord Dibben MS Excel MVP

Thanks

How do I do it for the whole file if I have mutiple sheets in it and do not
want to do it sheet by sheet?

Highlight the whole sheet by clicking on the box which intersects the
row and column labels, and then do Find and replace (CTRL-H):

Find what: [2005.xls]AA340
Replace with: [1995.xls]AB623
click replace all

Hope this helps.

Pete

I have several sheets in a file and cells in the sheets read data from
different other excel files. In some cells in each sheet (hard to find one by
one) the address is for example:

='D:\Gaines\Sourcefiles\Wet\Result\[2005.xls]AA340501'!E6

I want to change the address to:

='D:\Gaines\Sourcefiles\Wet\Result\[1995.xls]AB623501'!E6

I have to add that I have data in different cells not just E6. Instead of
cell by cell how can change the address (like above example) for the whole
sheet?

Appreciate your help