Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem opening hyperlink to PDF file in Excel 2003 | New Users to Excel | |||
Excel Outlook Attachments opening after several errors everytime | Setting up and Configuration of Excel | |||
Interesting TIF file problem | Excel Discussion (Misc queries) | |||
opening file problem please help | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions |