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?