Link to different Workbooks based in a reference

[plopes]

I have several columns that go to the same reference but each column t
a different sheet in another workbook.

For example:

A1 = '[AAA.xls]Sheet1'!$BT5/1000
B1 = '[AAA.xls]Sheet2'!$BT5/1000
C1 = '[AAA.xls]Sheet3'!$BT5/1000

How can I do it as I Have 15 Collumns refering to different Sheets.

I tried to do like this:

A1 = '[AAA.xls]INDIRECT(A10)'!$BT5/1000
B1 = '[AAA.xls]INDIRECT(B10)'!$BT5/1000
C1 = '[AAA.xls]INDIRECT(C10)'!$BT5/1000

whith

A10=Sheet1
B10=Sheet2
C10=Sheet3

But it doesn't work!

And What about if I want to pick values from different files?


Example:

A1 = '[AAA.xls]INDIRECT(A10)'!$BT5/1000
B1 = '[BBB.xls]INDIRECT(A10)'!$BT5/1000
C1 = '[CCC.xls]INDIRECT(A10)'!$BT5/1000

Can anyone tell me the solutions for both problems?

Pedro Lope

 

[Mark Graesser]

plopes,
Try:

= INDIRECT("[AAA.xls]"&A10&"!$BT5)/1000

Note that the other workbook must be open for the INDIRECT function to work.

Good Luck,
Mark Graesser
(e-mail address removed)

----- plopes > wrote: -----

I have several columns that go to the same reference but each column to
a different sheet in another workbook.

For example:

A1 = '[AAA.xls]Sheet1'!$BT5/1000
B1 = '[AAA.xls]Sheet2'!$BT5/1000
C1 = '[AAA.xls]Sheet3'!$BT5/1000

How can I do it as I Have 15 Collumns refering to different Sheets.

I tried to do like this:

A1 = '[AAA.xls]INDIRECT(A10)'!$BT5/1000
B1 = '[AAA.xls]INDIRECT(B10)'!$BT5/1000
C1 = '[AAA.xls]INDIRECT(C10)'!$BT5/1000

whith

A10=Sheet1
B10=Sheet2
C10=Sheet3

But it doesn't work!

And What about if I want to pick values from different files?


Example:

A1 = '[AAA.xls]INDIRECT(A10)'!$BT5/1000
B1 = '[BBB.xls]INDIRECT(A10)'!$BT5/1000
C1 = '[CCC.xls]INDIRECT(A10)'!$BT5/1000

Can anyone tell me the solutions for both problems?

Pedro Lopes

 

[Harlan Grove]

I have several columns that go to the same reference but each column to
a different sheet in another workbook.

For example:

A1 = '[AAA.xls]Sheet1'!$BT5/1000
B1 = '[AAA.xls]Sheet2'!$BT5/1000
C1 = '[AAA.xls]Sheet3'!$BT5/1000

How can I do it as I Have 15 Collumns refering to different Sheets.

I tried to do like this:

A1 = '[AAA.xls]INDIRECT(A10)'!$BT5/1000
B1 = '[AAA.xls]INDIRECT(B10)'!$BT5/1000
C1 = '[AAA.xls]INDIRECT(C10)'!$BT5/1000

...

Improper usage. The entire reference would need to be within the INDIRECT call.
Try

A1: =INRIRECT("'[AAA.xls]"&A10&"'!"&CELL("Address",$BT5))/1000

This only works with *open* workbooks. If you need to do this with closed
workbooks, see

http://www.google.com/[email protected]

 

[Harlan Grove]

...
...

= INDIRECT("[AAA.xls]"&A10&"!$BT5)/1000

...

You're missing a closing double quote after !$BT5 , so this should have been a
formula syntax error. You're also missing single quotes around the workbook and
worksheet names, which may not be a problem in this case, but is always a good
idea to include.

 

[Mark Graesser]

Thanks Harlan,
I usually test them before I post. Guess I'm getting cocky.

Regards,
Mark Graesser

----- Harlan Grove wrote: -----

...
...

= INDIRECT("[AAA.xls]"&A10&"!$BT5)/1000

...

You're missing a closing double quote after !$BT5 , so this should have been a
formula syntax error. You're also missing single quotes around the workbook and
worksheet names, which may not be a problem in this case, but is always a good
idea to include.