How to use =INDIRECT(A1 & "!B7") if B7 is in another worksheet on another
workbook? For example:
workbook name=List
worksheet name=n1

=INDIRECT(A1 & "!B7")

Where A1 =[List.xls]n1


--
Tim Zych
www.higherdata.com
Compare data in Excel and find differences with Workbook Compare
A free, powerful, flexible Excel utility



"INDIRECT" wrote in message
...
How to use =INDIRECT(A1 & "!B7") if B7 is in another worksheet on another
workbook? For example:
workbook name=List
worksheet name=n1

In this case we are assuming A1 is in another workbook but in fact A1 is in
the same workbook and B7 is in workbook List.
Should I put

=INDIRECT(A1 & "[list]A1!B7") ?

Thanks

Carla

"Tim Zych" escreveu:

=INDIRECT(A1 & "!B7")

Where A1 =[List.xls]n1


--
Tim Zych
www.higherdata.com
Compare data in Excel and find differences with Workbook Compare
A free, powerful, flexible Excel utility



"INDIRECT" wrote in message
...
How to use =INDIRECT(A1 & "!B7") if B7 is in another worksheet on another
workbook? For example:
workbook name=List
worksheet name=n1

What *exactly* are you trying to accomplish?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"INDIRECT" wrote in message
...
In this case we are assuming A1 is in another workbook but in fact A1 is
in
the same workbook and B7 is in workbook List.
Should I put

=INDIRECT(A1 & "[list]A1!B7") ?

Thanks

Carla

"Tim Zych" escreveu:

=INDIRECT(A1 & "!B7")

Where A1 =[List.xls]n1


--
Tim Zych
www.higherdata.com
Compare data in Excel and find differences with Workbook Compare
A free, powerful, flexible Excel utility



"INDIRECT" wrote in message
...
How to use =INDIRECT(A1 & "!B7") if B7 is in another worksheet on
another
workbook? For example:
workbook name=List
worksheet name=n1

INDIRECT wrote...
In this case we are assuming A1 is in another workbook but in fact
A1 is in the same workbook and B7 is in workbook List.
Should I put

=INDIRECT(A1 & "[list]A1!B7") ?
....

?

Are you trying to access cell B7 in a worksheet named A1 in a workbook
named List in the drive/directory given in cell A1 in the same
worksheet into which you're entering this formula? If so, you can't
use INDIRECT because INDIRECT only works with *OPEN* files.

The only time you need drive/directory to identify workbooks in Excel
is when those workbooks AREN'T open. Since Excel can only open one
file at a time with a given base filename (e.g., you can open either C:
\foo\file.xls or C:\bar\file.xls but not both), that file is always
unambiguously identified by its base filename alone. In your example
above,

=INDIRECT("[list]A1!B7")

uniquely identifies the *ONLY* external reference INDIRECT could
return.

If you need to access a given cell or block of cells in several other
CLOSED files, you have to use something other than INDIRECT. See the
following archived thread for the alternatives.

http://www.google.com/groups?selm=hk...wsranger.c om

I'm trying this:

I have a workbook named List with 25 worksheets named n1, n2, n3,...,n25.

I have another workbook named Final with 25 worksheets named f1, f2,
f3,...,f25.

In workbook Final, worksheet f1, cell C2, I want to build a formula to get
the value of the cell B7 (workbook List, worksheet n1).
In workbook Final, worksheet f2, cell C2, I want to build a formula to get
the value of the cell B7 (workbook List, worksheet n2).
In workbook Final, worksheet f3, cell C2, I want to build a formula to get
the value of the cell B7 (workbook List, worksheet n3).
And so on...

I'm trying write n1 in cell A1 (workbook List, worksheet f1), n2 in cell A1
(workbook List, worksheet f2), n3 in cell A1 (workbook List, worksheet f3)...
and use indirect but until now I couldn´t make it right :-(

I hope my explanion is beter now.

Thanks in advance.

Carla

"Harlan Grove" escreveu:

INDIRECT wrote...
In this case we are assuming A1 is in another workbook but in fact
A1 is in the same workbook and B7 is in workbook List.
Should I put

=INDIRECT(A1 & "[list]A1!B7") ?
....

?

Are you trying to access cell B7 in a worksheet named A1 in a workbook
named List in the drive/directory given in cell A1 in the same
worksheet into which you're entering this formula? If so, you can't
use INDIRECT because INDIRECT only works with *OPEN* files.

The only time you need drive/directory to identify workbooks in Excel
is when those workbooks AREN'T open. Since Excel can only open one
file at a time with a given base filename (e.g., you can open either C:
\foo\file.xls or C:\bar\file.xls but not both), that file is always
unambiguously identified by its base filename alone. In your example
above,

=INDIRECT("[list]A1!B7")

uniquely identifies the *ONLY* external reference INDIRECT could
return.

If you need to access a given cell or block of cells in several other
CLOSED files, you have to use something other than INDIRECT. See the
following archived thread for the alternatives.

http://www.google.com/groups?selm=hk...wsranger.c om

INDIRECT wrote...
I'm trying this:

I have a workbook named List with 25 worksheets named n1, n2,
n3,...,n25.

I have another workbook named Final with 25 worksheets named f1,
f2, f3,...,f25.

In workbook Final, worksheet f1, cell C2, I want to build a formula
to get the value of the cell B7 (workbook List, worksheet n1).
In workbook Final, worksheet f2, cell C2, I want to build a formula
to get the value of the cell B7 (workbook List, worksheet n2).
In workbook Final, worksheet f3, cell C2, I want to build a formula
to get the value of the cell B7 (workbook List, worksheet n3).
And so on...

Good. Not what you asked the time before this. You're learning about
details and precise specification. Good.

I'm going to assume that your worksheet names aren't really as simple
as n1, n2, f1, f2, etc. So best to create a table in the Final
workbook with worksheets names in Final workbook in the first column
and corresponding worksheet names in List workbook in the second
column. I'll assume this table is named Tbl.

Next, define the following names.

_CFN: =CELL("Filename",!$DX$32768)

_WSN: =MID(_CFN,FIND("]",_CFN)+1,32)

Then try the following formula.

[Final]f1!C2:
=INDIRECT("'[list]"&VLOOKUP(_WSN,Tbl,2,0)&"'!B7")

Copy this cell and paste into [Final]f2!C2, [Final]f3!C3, etc. The
single quotes in INDIRECT argument may not be strictly necessary, but
they'll save you lots of headaches in the long run.