Thread: formula referencing another workbook

Hello, I am trying to create a formula that references another spreadsheet. I
am able to do this at a manual level but what I am really looking to do is to
list a number of file names on one sheet and have the formula pull the file
names from that sheet and insert it into the reference formula. Here is an
example of what I have tried that has not worked:

=IF(ISERROR('[ Sheet1!E3 ]Approval Summary'!$C$13),0,'[ Sheet1!E3
]Approval Summary'!$C$13) + IF(ISERROR('[ Sheet1!E4 ]Approval
Summary'!$C$13),0,'[ Sheet1!E4 ]Approval Summary'!$C$13)

Where Sheet1!E3 would hold something like "abc.xls" If anyone can help with
this I would greatly appreciate it.

Thank You,
Steve

Hi

You can use INDIRECT function to return composed range. Like
=INDIRECT("'[" & Sheet1!E3 & "]Approval Summary'!$C$13)

But - INDIRECT works only, when the source workbook is opened at same time.
Otherwise an error is returned.

Arvi Laanemets


"Steve D" <Steve D@discussions.microsoft.com> wrote in message
news:DFC49AB1-1D9D-491E-A0BE-F26DF426CFDE@microsoft.com...
> Hello, I am trying to create a formula that references another
spreadsheet. I
> am able to do this at a manual level but what I am really looking to do is
to
> list a number of file names on one sheet and have the formula pull the
file
> names from that sheet and insert it into the reference formula. Here is an
> example of what I have tried that has not worked:
>
> =IF(ISERROR('[ Sheet1!E3 ]Approval Summary'!$C$13),0,'[ Sheet1!E3
> ]Approval Summary'!$C$13) + IF(ISERROR('[ Sheet1!E4 ]Approval
> Summary'!$C$13),0,'[ Sheet1!E4 ]Approval Summary'!$C$13)
>
> Where Sheet1!E3 would hold something like "abc.xls" If anyone can help
with
> this I would greatly appreciate it.
>
> Thank You,
> Steve

This may not be exactly what you are looking for but this
code may help you. It is inserted Alt F11 into MEO sheet...
Adjust line 12 and 34 to state your specific drill to your
Excel files on your computer.

Dim FSO As Object
Dim cnt As Long
Dim arfiles
Dim level As Long

Sub Folders()
Dim i As Long
Dim sFolder As String

Set FSO = CreateObject("Scripting.FileSystemObject")

arfiles = Array()
cnt = -1
level = 1

sFolder = "C:\Documents and Settings\MY OWN PATH!!!"
ReDim arfiles(1, 0)
If sFolder <> "" Then
SelectFiles sFolder
Worksheets.Add.Name = "Files"
With ActiveSheet
For i = LBound(arfiles, 2) To UBound(arfiles,
2)
.Hyperlinks.Add Anchor:=.Cells(i + 1,
arfiles(1, i)), _
Address:=arfiles(0,
i), _
TextToDisplay:=arfiles
(0, i)
Next
.Columns("A:Z").EntireColumn.AutoFit
End With
End If

End Sub

'----------------------------------------------------------
-------------
Sub SelectFiles(Optional sPath As String)
'----------------------------------------------------------
-------------
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object

If sPath = "" Then
Set FSO = CreateObject
("SCripting.FileSystemObject")
sPath = "c:\\Documents and Settings\MY OWN PATH"
End If

Set Folder = FSO.GetFolder(sPath)

Set Files = Folder.Files
For Each file In Files
cnt = cnt + 1
ReDim Preserve arfiles(1, cnt)
arfiles(0, cnt) = Folder.Path & "\" & file.Name
arfiles(1, cnt) = level
Next file

level = level + 1
For Each fldr In Folder.Subfolders
SelectFiles fldr.Path
Next

End Sub


Then try calling the information up using Sheet1!A1 for
example.

I'm using this code and it was supplied from this forum.
Sorry, the original author credit is buried somewhere in
my work!
HTH

>-----Original Message-----
>Hello, I am trying to create a formula that references
another spreadsheet. I
>am able to do this at a manual level but what I am really
looking to do is to
>list a number of file names on one sheet and have the
formula pull the file
>names from that sheet and insert it into the reference
formula. Here is an
>example of what I have tried that has not worked:
>
>=IF(ISERROR('[ Sheet1!E3 ]Approval Summary'!
$C$13),0,'[ Sheet1!E3
>]Approval Summary'!$C$13) + IF(ISERROR('[ Sheet1!E4 ]
Approval
>Summary'!$C$13),0,'[ Sheet1!E4 ]Approval Summary'!
$C$13)
>
>Where Sheet1!E3 would hold something like "abc.xls" If
anyone can help with
>this I would greatly appreciate it.
>
>Thank You,
>Steve
>.
>

=IF(ISERROR(INDIRECT("'["& Sheet1!E3&" ]Approval Summary'!$C$13")),0,indirect("'[" &Sheet1!E3& "]Approval Summary'!$C$13")) + IF(ISERROR(INDIRECT("'[" &Sheet1!E4& "]Approval Summary'!$C$13")),0,indirect("'["& Sheet1!E4 &"]Approval Summary'!$C$13"))