yes that does work. unfortunately for my application, the path always
changes. Thats why i defined "C:\something\" as a separate cell named
FilePath. I've tried:

='FilePath[NameOfFile.xls]Sheetname'!$B:$B

but that doesn't work for some reason...

Also, the name of the file changes quite frequently in terms of revision
date. I have also created another cell called RevisionDate that has
"(3-3-05)" in it. I've tried putting it in to the line as:

='["&"NameOfFile"&RevisionDate&".xls"&"]Sheetname'!$B:$B

which worked for the indirect command, but doesn't work for this... any
suggestions?


"PCLIVE" wrote:

For your file path, try using this syntax.

='C:\something\[NameOfFile.xls]Sheetname'!$B:$B

HTH,
Paul


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