problem after rename of file
 

"File1_2006.xls" is data input file (one year record)
"File2_2006.xls" is display file (extract info from above file) and contains
50sheets to display info by using =C:\[file1_2006.xls]details'!A1
=C:\[file1_2006.xls]details'!A2
=C:\[file1_2006.xls]details'!B5
........
........
=C:\[file1_2006.xls]details'!A10

For the year 2007, I delete all the info in "File1_2006.xls" and rename it
as "File1_2007.xls".
I also save "File2_2006.xls" under new file name "File2_2007.xls".

However, all the formulas in "File2_2007.xls" still link to "File1_2006.xls"
and is there a way where I can make all the formulas in "File2_2007.xls" to
link to "File1_2006.xls".

Or is there a better way to do it, 1 file for input and the other to display
info extract from the first .

Thanks

problem after rename of file
 

Edit / Replace

"vcff" wrote:

"File1_2006.xls" is data input file (one year record)
"File2_2006.xls" is display file (extract info from above file) and contains
50sheets to display info by using =C:\[file1_2006.xls]details'!A1
=C:\[file1_2006.xls]details'!A2
=C:\[file1_2006.xls]details'!B5
........
........
=C:\[file1_2006.xls]details'!A10

For the year 2007, I delete all the info in "File1_2006.xls" and rename it
as "File1_2007.xls".
I also save "File2_2006.xls" under new file name "File2_2007.xls".

However, all the formulas in "File2_2007.xls" still link to "File1_2006.xls"
and is there a way where I can make all the formulas in "File2_2007.xls" to
link to "File1_2006.xls".

Or is there a better way to do it, 1 file for input and the other to display
info extract from the first .

Thanks

problem after rename of file
 

Hi vcff

Try EditLinks in the menu bar if your Excel version have that option

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"vcff" wrote in message ...
"File1_2006.xls" is data input file (one year record)
"File2_2006.xls" is display file (extract info from above file) and contains
50sheets to display info by using =C:\[file1_2006.xls]details'!A1
=C:\[file1_2006.xls]details'!A2
=C:\[file1_2006.xls]details'!B5
........
........
=C:\[file1_2006.xls]details'!A10

For the year 2007, I delete all the info in "File1_2006.xls" and rename it
as "File1_2007.xls".
I also save "File2_2006.xls" under new file name "File2_2007.xls".

However, all the formulas in "File2_2007.xls" still link to "File1_2006.xls"
and is there a way where I can make all the formulas in "File2_2007.xls" to
link to "File1_2006.xls".

Or is there a better way to do it, 1 file for input and the other to display
info extract from the first .

Thanks

problem after rename of file
 

you mean edit / replace the formulas in "File2_2007.xls" .
will take me a lot of time, abt 39000 cells all with formula's link to
"File1_2007.xls"

I took a very long time to create it so I hope there is a way out to solve it.

"Marvin P. Winterbottom" wrote:

Edit / Replace

"vcff" wrote:

"File1_2006.xls" is data input file (one year record)
"File2_2006.xls" is display file (extract info from above file) and contains
50sheets to display info by using =C:\[file1_2006.xls]details'!A1
=C:\[file1_2006.xls]details'!A2
=C:\[file1_2006.xls]details'!B5
........
........
=C:\[file1_2006.xls]details'!A10

For the year 2007, I delete all the info in "File1_2006.xls" and rename it
as "File1_2007.xls".
I also save "File2_2006.xls" under new file name "File2_2007.xls".

However, all the formulas in "File2_2007.xls" still link to "File1_2006.xls"
and is there a way where I can make all the formulas in "File2_2007.xls" to
link to "File1_2006.xls".

Or is there a better way to do it, 1 file for input and the other to display
info extract from the first .

Thanks

problem after rename of file
 

Use find and replace file name

"Ron de Bruin" wrote:

Hi vcff

Try EditLinks in the menu bar if your Excel version have that option

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"vcff" wrote in message ...
"File1_2006.xls" is data input file (one year record)
"File2_2006.xls" is display file (extract info from above file) and contains
50sheets to display info by using =C:\[file1_2006.xls]details'!A1
=C:\[file1_2006.xls]details'!A2
=C:\[file1_2006.xls]details'!B5
........
........
=C:\[file1_2006.xls]details'!A10

For the year 2007, I delete all the info in "File1_2006.xls" and rename it
as "File1_2007.xls".
I also save "File2_2006.xls" under new file name "File2_2007.xls".

However, all the formulas in "File2_2007.xls" still link to "File1_2006.xls"
and is there a way where I can make all the formulas in "File2_2007.xls" to
link to "File1_2006.xls".

Or is there a better way to do it, 1 file for input and the other to display
info extract from the first .

Thanks

problem after rename of file
 

I think what Marvin means is to highlight all the cells with formulae
in and do CTRL-H to bring up the Find/Replace dialogue box. Use this to
Find: 2006 and Replace With: 2007 - you only need to do this in one
operation.

Hope this helps.

Pete

On Jan 24, 4:37 pm, vcff wrote:
you mean edit / replace the formulas in "File2_2007.xls" .
will take me a lot of time, abt 39000 cells all with formula's link to
"File1_2007.xls"

I took a very long time to create it so I hope there is a way out to solve it.



"Marvin P. Winterbottom" wrote:
Edit / Replace

"vcff" wrote:

"File1_2006.xls" is data input file (one year record)
"File2_2006.xls" is display file (extract info from above file) and contains
50sheets to display info by using =C:\[file1_2006.xls]details'!A1
=C:\[file1_2006.xls]details'!A2
=C:\[file1_2006.xls]details'!B5
........
........
=C:\[file1_2006.xls]details'!A10

For the year 2007, I delete all the info in "File1_2006.xls" and rename it
as "File1_2007.xls".
I also save "File2_2006.xls" under new file name "File2_2007.xls".

However, all the formulas in "File2_2007.xls" still link to "File1_2006.xls"
and is there a way where I can make all the formulas in "File2_2007.xls" to
link to "File1_2006.xls".

Or is there a better way to do it, 1 file for input and the other to display
info extract from the first .

Thanks- Hide quoted text -- Show quoted text -

problem after rename of file
 

Thank you for the help, I was able to complete the work in abt 5mins (auto
replace the formulas - in more than 200,000 cells, not 39000). I really
appreciate all the helps given to me.

Have a nice day.
vcff



"Pete_UK" wrote:

I think what Marvin means is to highlight all the cells with formulae
in and do CTRL-H to bring up the Find/Replace dialogue box. Use this to
Find: 2006 and Replace With: 2007 - you only need to do this in one
operation.

Hope this helps.

Pete

On Jan 24, 4:37 pm, vcff wrote:
you mean edit / replace the formulas in "File2_2007.xls" .
will take me a lot of time, abt 39000 cells all with formula's link to
"File1_2007.xls"

I took a very long time to create it so I hope there is a way out to solve it.



"Marvin P. Winterbottom" wrote:
Edit / Replace

"vcff" wrote:

"File1_2006.xls" is data input file (one year record)
"File2_2006.xls" is display file (extract info from above file) and contains
50sheets to display info by using =C:\[file1_2006.xls]details'!A1
=C:\[file1_2006.xls]details'!A2
=C:\[file1_2006.xls]details'!B5
........
........
=C:\[file1_2006.xls]details'!A10

For the year 2007, I delete all the info in "File1_2006.xls" and rename it
as "File1_2007.xls".
I also save "File2_2006.xls" under new file name "File2_2007.xls".

However, all the formulas in "File2_2007.xls" still link to "File1_2006.xls"
and is there a way where I can make all the formulas in "File2_2007.xls" to
link to "File1_2006.xls".

Or is there a better way to do it, 1 file for input and the other to display
info extract from the first .

Thanks- Hide quoted text -- Show quoted text -

problem after rename of file
 

Thanks for feeding back - glad it worked for you.

Pete

On Jan 25, 12:51 am, vcff wrote:
Thank you for the help, I was able to complete the work in abt 5mins (auto
replace the formulas - in more than 200,000 cells, not 39000). I really
appreciate all the helps given to me.

Have a nice day.
vcff



"Pete_UK" wrote:
I think what Marvin means is to highlight all the cells with formulae
in and do CTRL-H to bring up the Find/Replace dialogue box. Use this to
Find: 2006 and Replace With: 2007 - you only need to do this in one
operation.

Hope this helps.

Pete

On Jan 24, 4:37 pm, vcff wrote:
you mean edit / replace the formulas in "File2_2007.xls" .
will take me a lot of time, abt 39000 cells all with formula's link to
"File1_2007.xls"

I took a very long time to create it so I hope there is a way out to solve it.

"Marvin P. Winterbottom" wrote:
Edit / Replace

"vcff" wrote:

"File1_2006.xls" is data input file (one year record)
"File2_2006.xls" is display file (extract info from above file) and contains
50sheets to display info by using =C:\[file1_2006.xls]details'!A1
=C:\[file1_2006.xls]details'!A2
=C:\[file1_2006.xls]details'!B5
........
........
=C:\[file1_2006.xls]details'!A10

For the year 2007, I delete all the info in "File1_2006.xls" and rename it
as "File1_2007.xls".
I also save "File2_2006.xls" under new file name "File2_2007.xls".

However, all the formulas in "File2_2007.xls" still link to "File1_2006.xls"
and is there a way where I can make all the formulas in "File2_2007.xls" to
link to "File1_2006.xls".

Or is there a better way to do it, 1 file for input and the other to display
info extract from the first .

Thanks- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -