Dynamic reference to workbook
 

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

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

I considered this also, when I get to the second one how would it look? like
this?

=indirect("'C:\dir\["&A1&"].sheet1'!$A$1") -
indirect("'C:\dir\["&B1&"].sheet1'!$A$1") -
indirect("'C:\dir\["&C1&"].sheet1'!$A$1")

"bj" wrote:

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

OK, I figured out how to do math with 2 indirects, but can't seem to get my
indirect to a dynamic workbook to work

I have this cell reference that works and returns the correct value
='E:\EcoModel\[CallingA.xls]A'!$A$1

I try to wrap the indirect around it as in the example bj gave, and it
doesn't recognize the cell reference
A1 = CallingA.xls
=INDIRECT("'E:\ecomodel\["&A1&"]A'!$A$1")

My quotes match, and I removed the . since I didn't see it in any other
examples I can find.



"Misssy" wrote:

I considered this also, when I get to the second one how would it look? like
this?

=indirect("'C:\dir\["&A1&"].sheet1'!$A$1") -
indirect("'C:\dir\["&B1&"].sheet1'!$A$1") -
indirect("'C:\dir\["&C1&"].sheet1'!$A$1")

"bj" wrote:

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

I don't know if it makes any difference, but the capital structure in the
working equaiton and the non working equation are different.
for the equation that works
='E:\EcoModel\[CallingA.xls]A'!$A$1
try
=indirect("'E:\EcoModel\[CallingA.xls]A'!$A$1")
I have also had them work by adding or deleting the ".xls"
I have no clue why.

"Misssy" wrote:

OK, I figured out how to do math with 2 indirects, but can't seem to get my
indirect to a dynamic workbook to work

I have this cell reference that works and returns the correct value
='E:\EcoModel\[CallingA.xls]A'!$A$1

I try to wrap the indirect around it as in the example bj gave, and it
doesn't recognize the cell reference
A1 = CallingA.xls
=INDIRECT("'E:\ecomodel\["&A1&"]A'!$A$1")

My quotes match, and I removed the . since I didn't see it in any other
examples I can find.



"Misssy" wrote:

I considered this also, when I get to the second one how would it look? like
this?

=indirect("'C:\dir\["&A1&"].sheet1'!$A$1") -
indirect("'C:\dir\["&B1&"].sheet1'!$A$1") -
indirect("'C:\dir\["&C1&"].sheet1'!$A$1")

"bj" wrote:

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