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?