if the add-on works, then that would be fantastic. however, my company has
strict rules about downloading add-ons... any other suggestions? your time
and efforts are much appreciated.



"Bob Phillips" wrote:

Normally you would use iNDIRECT, but INDIRECT doe not work with closed
workbooks.

You could try Harlan Grove's PULL function. You can get it
ftp://members.aol.com/hrlngrv/ (look for pull.zip)

You would use it like so

=pull("'"&A3&"\["&B3&".xls]"&C3&"'!"&"B5")

or

=pull("'"&A3&"\["&B3&".xls]"&C3&"'!B5")

Alternative

As for alternatives, try the INDIRECT.EXT function in Laurent Longre's
MOREFUNC.XLL add-in, available from


http://xcell05.free.fr/english/


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"SUMIF Help" <SUMIF wrote in message
...
I really need some help. I'm trying to use the SUMIF command to retrieve a
value that is at the end of a list. The corresponding cell for the last
cell
will always be called "End". For the example given below, i'm trying to
get
the 501 value.

Tom 500
Bob 632
Dan 154
Ted 45
End 501

in my case, i am trying to reference to a completely different file.
Right
now, my command cell looks something like this:

SUMIF('[NameOfFile.xls]Sheetname'!$A:$A, "End",
'[NameOfFile.xls]Sheetname'!$B:$B)

which works fine. But I need to add a dynamic address to this command. I
have a different cell named FilePath that equals to "C:\something\". I
tried
placing that before the brackets that refer to the file name because that
worked for the INDIRECT function. However, i've messed with the syntax
for a
while and i'm stuck. I also know the file has to be open for the
reference
to work correctly.

Also, i would like to add a dynamic reference to the file name. I have
another cell named RevisionDate which equals (3-3-05) and would like to
add
that in [NameOfFile.xls] somewhere.

Any ideas?