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

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

On Sep 3, 12:22*am, Darius wrote:
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

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?

"Pete_UK" wrote:

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

On Sep 3, 12:22 am, Darius wrote:
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

In find/replace options select Workbook.


Gord Dibben MS Excel MVP

On Thu, 3 Sep 2009 07:13:01 -0700, Darius
wrote:

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?

"Pete_UK" wrote:

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

On Sep 3, 12:22 am, Darius wrote:
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