concider something like the indirect function
in column A place your day1,day2, etc
in column B
=indirect("'C:\dir\["&A1&"].sheet1'!$A$1")


"Misssy" wrote:

> I have some formulas and need an easy way to switch workbooks but keep the
> same cell reference on the different workbooks. Example
> first run 'C:\dir\[day1].sheet1'!A1
> second run 'C:\dir\[day2].sheet1'!A1
> thrid run 'C:\dir\[day2].sheet1'!A1
>
> How could I change between workbook day1, day2 or day3 without doing a
> search and replace?
>
> Also, the formulas are more complicated than my example, there may be 4 or 5
> workbook references within a formula. I tried using TEXT and letting the
> text reference hold the changing workbook, but don't see how to make this
> work when there are so many places it would have to be.
>
> Here is a real example - and next time I run I would need workbook
> [elast.xls] to be [elast1.xls]
>
> =+Solver!$A$61-shocks!$H$6-shocks!$H$50-'C:\Dir\[elast.xls]Sheet1'!$C$12*Solver!$A$63-'C:\Dir\[elast.xls]Sheet1'!$D$12*Solver!$A$64-'C:\Dir\[elast.xls]Sheet1'!$E$12*Solver!$A$68-'C:\Dir\[elast.xls]Sheet1'!$F$12*Solver!$A$66-'C:\Dir\[elast.xls]Sheet1'!$G$12*Solver!$A$70-'C:\Dir\[elast.xls]Sheet1'!$H$12*Solver!$A$69-'C:\Dir\[elast.xls]Sheet1'!$I$12*Solver!$A$72-'C:\Dir\[elast.xls]Sheet1'!$J$12*Solver!$A$68-'C:\Dir\[elast.xls]Sheet1'!$K$12*Solver!$A$71-'C:\Dir\[elast.xls]Sheet1'!$L$12*Solver!$A$67