Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenating cells to produce a cell ref from another excel file | Excel Worksheet Functions | |||
cannot load excel file - "too many different cell formats" error s | Excel Discussion (Misc queries) | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Excel: how to formulate conditional cell references | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |