|
COPY CELL WITH FILE REFERENCES IN EXCEL
I need to know how to make excel automatically change this when I copy down:
='C:\CP Files\[COT010.xls]Sheet1'!$E$40 to ='C:\CP Files\[COT011.xls]Sheet1'!$E$40 and so on . . . ='C:\CP Files\[COT012.xls]Sheet1'!$E$40 ='C:\CP Files\[COT013.xls]Sheet1'!$E$40 ='C:\CP Files\[COT014.xls]Sheet1'!$E$40 etc . . . Since it is a reference to another file, it will not automatically change the file reference when I fill down. Hopefully this makes sense. Can anyone help me? |
Hi natelee78
I always use a macro if I want this http://www.rondebruin.nl/copy1.htm Maybe you can use it also -- Regards Ron de Bruin http://www.rondebruin.nl "natelee78" wrote in message ... I need to know how to make excel automatically change this when I copy down: ='C:\CP Files\[COT010.xls]Sheet1'!$E$40 to ='C:\CP Files\[COT011.xls]Sheet1'!$E$40 and so on . . . ='C:\CP Files\[COT012.xls]Sheet1'!$E$40 ='C:\CP Files\[COT013.xls]Sheet1'!$E$40 ='C:\CP Files\[COT014.xls]Sheet1'!$E$40 etc . . . Since it is a reference to another file, it will not automatically change the file reference when I fill down. Hopefully this makes sense. Can anyone help me? |
Just to add to Ron's solution
you could do this with Indirect except you are linking to a closed workbook and indirect doesn't support links to closed workbooks. You could use formula to build your formula strings as the result, then select the range of cells, and do Edit=Copy, then do Edit=Paste Special and select values to replace the formulas with the results. then with the cells still selected, do edit=replace what = with = this will cause Excel to convert them to formulas. the formula would be = "='C:\CP Files\[COT0" & row(A10) & ".xls]Sheet1'!$E$40" drag fill that down. -- Regards, Tom Ogilvy "natelee78" wrote in message ... I need to know how to make excel automatically change this when I copy down: ='C:\CP Files\[COT010.xls]Sheet1'!$E$40 to ='C:\CP Files\[COT011.xls]Sheet1'!$E$40 and so on . . . ='C:\CP Files\[COT012.xls]Sheet1'!$E$40 ='C:\CP Files\[COT013.xls]Sheet1'!$E$40 ='C:\CP Files\[COT014.xls]Sheet1'!$E$40 etc . . . Since it is a reference to another file, it will not automatically change the file reference when I fill down. Hopefully this makes sense. Can anyone help me? |
Sorry, I send the wrong link
http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi natelee78 I always use a macro if I want this http://www.rondebruin.nl/copy1.htm Maybe you can use it also -- Regards Ron de Bruin http://www.rondebruin.nl "natelee78" wrote in message ... I need to know how to make excel automatically change this when I copy down: ='C:\CP Files\[COT010.xls]Sheet1'!$E$40 to ='C:\CP Files\[COT011.xls]Sheet1'!$E$40 and so on . . . ='C:\CP Files\[COT012.xls]Sheet1'!$E$40 ='C:\CP Files\[COT013.xls]Sheet1'!$E$40 ='C:\CP Files\[COT014.xls]Sheet1'!$E$40 etc . . . Since it is a reference to another file, it will not automatically change the file reference when I fill down. Hopefully this makes sense. Can anyone help me? |
I'm tried what you wrote, but it didn't work. Perhaps I'm not doing
something right? In your example, what does "&row(A10)&" refer to? "Tom Ogilvy" wrote: Just to add to Ron's solution you could do this with Indirect except you are linking to a closed workbook and indirect doesn't support links to closed workbooks. You could use formula to build your formula strings as the result, then select the range of cells, and do Edit=Copy, then do Edit=Paste Special and select values to replace the formulas with the results. then with the cells still selected, do edit=replace what = with = this will cause Excel to convert them to formulas. the formula would be = "='C:\CP Files\[COT0" & row(A10) & ".xls]Sheet1'!$E$40" drag fill that down. -- Regards, Tom Ogilvy "natelee78" wrote in message ... I need to know how to make excel automatically change this when I copy down: ='C:\CP Files\[COT010.xls]Sheet1'!$E$40 to ='C:\CP Files\[COT011.xls]Sheet1'!$E$40 and so on . . . ='C:\CP Files\[COT012.xls]Sheet1'!$E$40 ='C:\CP Files\[COT013.xls]Sheet1'!$E$40 ='C:\CP Files\[COT014.xls]Sheet1'!$E$40 etc . . . Since it is a reference to another file, it will not automatically change the file reference when I fill down. Hopefully this makes sense. Can anyone help me? |
| All times are GMT +1. The time now is 08:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com