changing the address of data in a cell

  • Thread starter Thread starter Darius
  • Start date Start date
D

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
 
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
 
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 said:
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
Click to expand...
Click to expand...
 
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?

Pete_UK said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top