COPY CELL WITH FILE REFERENCES IN EXCEL

N

natelee78

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?
 
T

Tom Ogilvy

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.
 
R

Ron de Bruin

Sorry, I send the wrong link
http://www.rondebruin.nl/summary2.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


Ron de Bruin said:
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 said:
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?
Click to expand...
Click to expand...
 
N

natelee78

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