Link to another workbook & indirect

  • Thread starter Thread starter Jordon
  • Start date Start date
J

Jordon

I've been playing around with =indirect but I can't get it
to work with linking to a different workbook.

I have a workbook that tracks fuel usage for about 50 trucks.
There are as many sheets as there are trucks and each sheet's
name is the truck's number. That number is always in A1, by
way of a formula.

With the new year I've created a new workbook with no data
and I'm looking to copy each trucks mileage from last years
workbook to the new one.

='[Fuel2009.xls]107'!$H$2 links to the correct cell but what
I'm looking to do is to use the contents of A1 (which is 107)
to get it to pull what's in H2 on the 107 sheet in Fuel2009.xls.
 
Try this
=INDIRECT("'[Fuel2009.xls]"&A1&"'!$H$2")

after the first ( we have: double quote " followed by single quote '
after the second & we gave the same: double quote " followed by single quote
'

Remember that Fuel2009 must be open
best wishes
 
=indirect() won't work if the sending file is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

===
If you have trouble getting to the site, then search google for indirect.ext.

I found this alternative site:
http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

I didn't look to see if it was the most current version.

I'd check the original site every so often to see if it's working.
I've been playing around with =indirect but I can't get it
to work with linking to a different workbook.

I have a workbook that tracks fuel usage for about 50 trucks.
There are as many sheets as there are trucks and each sheet's
name is the truck's number. That number is always in A1, by
way of a formula.

With the new year I've created a new workbook with no data
and I'm looking to copy each trucks mileage from last years
workbook to the new one.

='[Fuel2009.xls]107'!$H$2 links to the correct cell but what
I'm looking to do is to use the contents of A1 (which is 107)
to get it to pull what's in H2 on the 107 sheet in Fuel2009.xls.
Click to expand...
 
That's fine. After I got the correct figures into it
a copy/paste special/values does what I needed it to
do.

Jordon

Dave said:
=indirect() won't work if the sending file is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

===
If you have trouble getting to the site, then search google for indirect.ext.

I found this alternative site:
http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

I didn't look to see if it was the most current version.

I'd check the original site every so often to see if it's working.
I've been playing around with =indirect but I can't get it
to work with linking to a different workbook.

I have a workbook that tracks fuel usage for about 50 trucks.
There are as many sheets as there are trucks and each sheet's
name is the truck's number. That number is always in A1, by
way of a formula.

With the new year I've created a new workbook with no data
and I'm looking to copy each trucks mileage from last years
workbook to the new one.

='[Fuel2009.xls]107'!$H$2 links to the correct cell but what
I'm looking to do is to use the contents of A1 (which is 107)
to get it to pull what's in H2 on the 107 sheet in Fuel2009.xls.
Click to expand...
Click to expand...
 
Back
Top