Dynamic reference to workbook

Misssy · 09-06-2005, 06:05 AM

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

bj · 09-06-2005, 06:05 AM

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

bj · 09-06-2005, 12:05 AM

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