Link to another workbook & indirect


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.
 
Ad

Advertisements

B

Bernard Liengme

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
 
D

Dave Peterson

=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.
 
J

Jordon

It's the first double and single quote that I wasn't getting.

Thanks Bernard!

Jordon
 
Ad

Advertisements

J

Jordon

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top