INDIRECT

[INDIRECT]

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

 

[Tim Zych]

=INDIRECT(A1 & "!B7")

Where A1 = [List.xls]n1

 

[INDIRECT]

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 & "

• 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
  
  
  
  

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

 

[Ragdyer]

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

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 & "

• 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
  
  
  
  

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

 

[Harlan Grove]

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 & "

• 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("
  • 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/[email protected]

 

[INDIRECT]

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:

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 & "

• 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("
  • 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/[email protected]

 

[Harlan Grove]

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("'

• "&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.