SUMIF Help

[Guest]

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?

 

[PCLIVE]

For your file path, try using this syntax.

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

HTH,
Paul

 

[Bob Phillips]

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)

 

[Guest]

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?

For your file path, try using this syntax.

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

HTH,
Paul

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?

 

[Guest]

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.

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)

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?

 

[Bob Phillips]

Harlan's code is not an add-on, although you would need to add the code to
your workbook.

--
---
HTH

Bob

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

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.

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)

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?