Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF Help
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF Help
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF Help
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF Help
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF Help
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) "SUMIF Help" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif | Excel Discussion (Misc queries) | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
sumif | Excel Discussion (Misc queries) | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |