|
Building Dynamic SUMIF statement
I hope this is an easy question to answer...
I am attempting to use a SUMIF to pull in numbers from a separate timesheet and I would like to build part of the external reference workbook name from a cell instead of hard-coding it. For example: =SUMIF('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22,C6,'[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22) I have a cell with the resource name and I would like to plug in the name from that cell for FirstName_LastName but I cant seem to get that to work. Any thoughts? |
Building Dynamic SUMIF statement
You've got a couple of obstacles to overcome.
First... What you'd want to use is =indirect() and build a formula that results in the string that points at the folder, file, sheet, location. But the bad thing is that =indirect() won't work if that sending file is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Second... =sumif() will return an error if the workbook is closed. You could replace it with =sumproduct() =sumproduct(--('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22=C6), ('[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22)) But you'd still may have to overcome the closed workbook problem. ps. You can't use the entire column in the =sumproduct() formula unless you're using xl2007. Nancy Taylor wrote: I hope this is an easy question to answer... I am attempting to use a SUMIF to pull in numbers from a separate timesheet and I would like to build part of the external reference workbook name from a cell instead of hard-coding it. For example: =SUMIF('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22,C6,'[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22) I have a cell with the resource name and I would like to plug in the name from that cell for FirstName_LastName but I cant seem to get that to work. Any thoughts? -- Dave Peterson |
Building Dynamic SUMIF statement
Hi Dave,
I haven't used the Indirect function before so I've been playing with it to see how it works. I've been able to get the SUMIF statement built but I am getting that annoying #REF! error. I do have the workbook that I am referencing open and I have used the debug to step into the formula to make sure everything is resolving correctly. Looks OK but I can't get it to work. In case you can spot something easy that I can fix, here's my new formula: =INDIRECT("SUMIF('["&$D$6&".xlsx]Weekly Timesheet'!$L$2:$L$19,"&C6&",'["&$D$6&".xlsx]Weekly Timesheet'!$J$2:$J$19)") I'll keep trying... Thanks for the tip! Nancy "Dave Peterson" wrote: You've got a couple of obstacles to overcome. First... What you'd want to use is =indirect() and build a formula that results in the string that points at the folder, file, sheet, location. But the bad thing is that =indirect() won't work if that sending file is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Second... =sumif() will return an error if the workbook is closed. You could replace it with =sumproduct() =sumproduct(--('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22=C6), ('[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22)) But you'd still may have to overcome the closed workbook problem. ps. You can't use the entire column in the =sumproduct() formula unless you're using xl2007. Nancy Taylor wrote: I hope this is an easy question to answer... I am attempting to use a SUMIF to pull in numbers from a separate timesheet and I would like to build part of the external reference workbook name from a cell instead of hard-coding it. For example: =SUMIF('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22,C6,'[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22) I have a cell with the resource name and I would like to plug in the name from that cell for âœFirstName_LastNameâ but I canât seem to get that to work. Any thoughts? -- Dave Peterson |
Building Dynamic SUMIF statement
Put the INDIRECT inside the SUMIF, not the other way around.
Nancy Taylor wrote: Hi Dave, I haven't used the Indirect function before so I've been playing with it to see how it works. I've been able to get the SUMIF statement built but I am getting that annoying #REF! error. I do have the workbook that I am referencing open and I have used the debug to step into the formula to make sure everything is resolving correctly. Looks OK but I can't get it to work. In case you can spot something easy that I can fix, here's my new formula: =INDIRECT("SUMIF('["&$D$6&".xlsx]Weekly Timesheet'!$L$2:$L$19,"&C6&",'["&$D$6&".xlsx]Weekly Timesheet'!$J$2:$J$19)") I'll keep trying... Thanks for the tip! Nancy "Dave Peterson" wrote: You've got a couple of obstacles to overcome. First... What you'd want to use is =indirect() and build a formula that results in the string that points at the folder, file, sheet, location. But the bad thing is that =indirect() won't work if that sending file is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Second... =sumif() will return an error if the workbook is closed. You could replace it with =sumproduct() =sumproduct(--('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22=C6), ('[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22)) But you'd still may have to overcome the closed workbook problem. ps. You can't use the entire column in the =sumproduct() formula unless you're using xl2007. Nancy Taylor wrote: I hope this is an easy question to answer... I am attempting to use a SUMIF to pull in numbers from a separate timesheet and I would like to build part of the external reference workbook name from a cell instead of hard-coding it. For example: =SUMIF('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22,C6,'[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22) I have a cell with the resource name and I would like to plug in the name from that cell for âœFirstName_LastNameâ but I canât seem to get that to work. Any thoughts? -- Dave Peterson |
Building Dynamic SUMIF statement
Just to add to Glenn's response:
=sumif(indirect("'["&$d$6&".xlsx]weekly timesheet'!l2:l19"),c6, indirect("'["&$d$6&".xlsx]weekly timesheet'!j2:j19")) (I didn't test it, so watch out for my typos!) Since L2:L19 and J2:J19 are within doublequotes (and are strings, the $'s weren't necessary. If you still have trouble, share what's in D6, too. It should be just the name (without the extension and without the drive and path). Nancy Taylor wrote: Hi Dave, I haven't used the Indirect function before so I've been playing with it to see how it works. I've been able to get the SUMIF statement built but I am getting that annoying #REF! error. I do have the workbook that I am referencing open and I have used the debug to step into the formula to make sure everything is resolving correctly. Looks OK but I can't get it to work. In case you can spot something easy that I can fix, here's my new formula: =INDIRECT("SUMIF('["&$D$6&".xlsx]Weekly Timesheet'!$L$2:$L$19,"&C6&",'["&$D$6&".xlsx]Weekly Timesheet'!$J$2:$J$19)") I'll keep trying... Thanks for the tip! Nancy "Dave Peterson" wrote: You've got a couple of obstacles to overcome. First... What you'd want to use is =indirect() and build a formula that results in the string that points at the folder, file, sheet, location. But the bad thing is that =indirect() won't work if that sending file is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Second... =sumif() will return an error if the workbook is closed. You could replace it with =sumproduct() =sumproduct(--('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22=C6), ('[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22)) But you'd still may have to overcome the closed workbook problem. ps. You can't use the entire column in the =sumproduct() formula unless you're using xl2007. Nancy Taylor wrote: I hope this is an easy question to answer... I am attempting to use a SUMIF to pull in numbers from a separate timesheet and I would like to build part of the external reference workbook name from a cell instead of hard-coding it. For example: =SUMIF('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22,C6,'[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22) I have a cell with the resource name and I would like to plug in the name from that cell for âœFirstName_LastNameâ but I canât seem to get that to work. Any thoughts? -- Dave Peterson -- Dave Peterson |
Building Dynamic SUMIF statement
Beautiful! Thank you so much for your help! I appreciate it soooo much!
I can call it a day now and enjoy my weekend without this pesky problem keeping me up all night! Hope you have a wonderful weekend! Nancy "Dave Peterson" wrote: Just to add to Glenn's response: =sumif(indirect("'["&$d$6&".xlsx]weekly timesheet'!l2:l19"),c6, indirect("'["&$d$6&".xlsx]weekly timesheet'!j2:j19")) (I didn't test it, so watch out for my typos!) Since L2:L19 and J2:J19 are within doublequotes (and are strings, the $'s weren't necessary. If you still have trouble, share what's in D6, too. It should be just the name (without the extension and without the drive and path). Nancy Taylor wrote: Hi Dave, I haven't used the Indirect function before so I've been playing with it to see how it works. I've been able to get the SUMIF statement built but I am getting that annoying #REF! error. I do have the workbook that I am referencing open and I have used the debug to step into the formula to make sure everything is resolving correctly. Looks OK but I can't get it to work. In case you can spot something easy that I can fix, here's my new formula: =INDIRECT("SUMIF('["&$D$6&".xlsx]Weekly Timesheet'!$L$2:$L$19,"&C6&",'["&$D$6&".xlsx]Weekly Timesheet'!$J$2:$J$19)") I'll keep trying... Thanks for the tip! Nancy "Dave Peterson" wrote: You've got a couple of obstacles to overcome. First... What you'd want to use is =indirect() and build a formula that results in the string that points at the folder, file, sheet, location. But the bad thing is that =indirect() won't work if that sending file is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Second... =sumif() will return an error if the workbook is closed. You could replace it with =sumproduct() =sumproduct(--('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22=C6), ('[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22)) But you'd still may have to overcome the closed workbook problem. ps. You can't use the entire column in the =sumproduct() formula unless you're using xl2007. Nancy Taylor wrote: I hope this is an easy question to answer... I am attempting to use a SUMIF to pull in numbers from a separate timesheet and I would like to build part of the external reference workbook name from a cell instead of hard-coding it. For example: =SUMIF('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22,C6,'[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22) I have a cell with the resource name and I would like to plug in the name from that cell for ââ¬ÅFirstName_LastNameââ¬Â but I canââ¬â¢t seem to get that to work. Any thoughts? -- Dave Peterson -- Dave Peterson |
Building Dynamic SUMIF statement
Hi Dave,
Well, I have gone through and replaced my sumifs with sumproducts and downloaded the morefunc so that I can take advantage of the indirect.ext, but my formulas still give me a #REF! if the associated spreadsheets are closed. Is there a trick to getting the indirect.ext to work? Nancy "Dave Peterson" wrote: You've got a couple of obstacles to overcome. First... What you'd want to use is =indirect() and build a formula that results in the string that points at the folder, file, sheet, location. But the bad thing is that =indirect() won't work if that sending file is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Second... =sumif() will return an error if the workbook is closed. You could replace it with =sumproduct() =sumproduct(--('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22=C6), ('[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22)) But you'd still may have to overcome the closed workbook problem. ps. You can't use the entire column in the =sumproduct() formula unless you're using xl2007. Nancy Taylor wrote: I hope this is an easy question to answer... I am attempting to use a SUMIF to pull in numbers from a separate timesheet and I would like to build part of the external reference workbook name from a cell instead of hard-coding it. For example: =SUMIF('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22,C6,'[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22) I have a cell with the resource name and I would like to plug in the name from that cell for âœFirstName_LastNameâ but I canât seem to get that to work. Any thoughts? -- Dave Peterson |
Building Dynamic SUMIF statement
For me, it's more trial and error.
If you can't get it working, share the formula you used. (I'd get the simple =indirect.ext() working first.) And share the values in each of the cells that the formula refers to. I'm sure you'll get some suggestions. Nancy Taylor wrote: Hi Dave, Well, I have gone through and replaced my sumifs with sumproducts and downloaded the morefunc so that I can take advantage of the indirect.ext, but my formulas still give me a #REF! if the associated spreadsheets are closed. Is there a trick to getting the indirect.ext to work? Nancy "Dave Peterson" wrote: You've got a couple of obstacles to overcome. First... What you'd want to use is =indirect() and build a formula that results in the string that points at the folder, file, sheet, location. But the bad thing is that =indirect() won't work if that sending file is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Second... =sumif() will return an error if the workbook is closed. You could replace it with =sumproduct() =sumproduct(--('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22=C6), ('[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22)) But you'd still may have to overcome the closed workbook problem. ps. You can't use the entire column in the =sumproduct() formula unless you're using xl2007. Nancy Taylor wrote: I hope this is an easy question to answer... I am attempting to use a SUMIF to pull in numbers from a separate timesheet and I would like to build part of the external reference workbook name from a cell instead of hard-coding it. For example: =SUMIF('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22,C6,'[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22) I have a cell with the resource name and I would like to plug in the name from that cell for âœFirstName_LastNameâ but I canât seem to get that to work. Any thoughts? -- Dave Peterson -- Dave Peterson |
Building Dynamic SUMIF statement
Not sure if my last post worked so trying again...
Here's my formula: =SUMPRODUCT(--(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]Weekly Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!$L:$L"))) B2 = 09122009 D6 = Nancy Taylor C6 = Project and phase name I need to total column N = timesheet project and phase names column L = timesheet hours It seems to work if I have the detail spreadsheets open before I open the summary spreadsheet but if the detail spreadsheets are closed, the #REF! comes up. If I then open the detail sheets, the #REF! resolves itself. Make any sense to anyone? "Dave Peterson" wrote: For me, it's more trial and error. If you can't get it working, share the formula you used. (I'd get the simple =indirect.ext() working first.) And share the values in each of the cells that the formula refers to. I'm sure you'll get some suggestions. Nancy Taylor wrote: Hi Dave, Well, I have gone through and replaced my sumifs with sumproducts and downloaded the morefunc so that I can take advantage of the indirect.ext, but my formulas still give me a #REF! if the associated spreadsheets are closed. Is there a trick to getting the indirect.ext to work? Nancy "Dave Peterson" wrote: You've got a couple of obstacles to overcome. First... What you'd want to use is =indirect() and build a formula that results in the string that points at the folder, file, sheet, location. But the bad thing is that =indirect() won't work if that sending file is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Second... =sumif() will return an error if the workbook is closed. You could replace it with =sumproduct() =sumproduct(--('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22=C6), ('[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22)) But you'd still may have to overcome the closed workbook problem. ps. You can't use the entire column in the =sumproduct() formula unless you're using xl2007. Nancy Taylor wrote: I hope this is an easy question to answer... I am attempting to use a SUMIF to pull in numbers from a separate timesheet and I would like to build part of the external reference workbook name from a cell instead of hard-coding it. For example: =SUMIF('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22,C6,'[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22) I have a cell with the resource name and I would like to plug in the name from that cell for ââ¬ÅFirstName_LastNameââ¬Â but I canââ¬â¢t seem to get that to work. Any thoughts? -- Dave Peterson -- Dave Peterson |
Building Dynamic SUMIF statement
As Dave and I both said, INDIRECT does NOT work with closed workbooks. !!!
-- Don Guillett Microsoft MVP Excel SalesAid Software "Nancy Taylor" wrote in message ... Not sure if my last post worked so trying again... Here's my formula: =SUMPRODUCT(--(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]Weekly Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!$L:$L"))) B2 = 09122009 D6 = Nancy Taylor C6 = Project and phase name I need to total column N = timesheet project and phase names column L = timesheet hours It seems to work if I have the detail spreadsheets open before I open the summary spreadsheet but if the detail spreadsheets are closed, the #REF! comes up. If I then open the detail sheets, the #REF! resolves itself. Make any sense to anyone? "Dave Peterson" wrote: For me, it's more trial and error. If you can't get it working, share the formula you used. (I'd get the simple =indirect.ext() working first.) And share the values in each of the cells that the formula refers to. I'm sure you'll get some suggestions. Nancy Taylor wrote: Hi Dave, Well, I have gone through and replaced my sumifs with sumproducts and downloaded the morefunc so that I can take advantage of the indirect.ext, but my formulas still give me a #REF! if the associated spreadsheets are closed. Is there a trick to getting the indirect.ext to work? Nancy "Dave Peterson" wrote: You've got a couple of obstacles to overcome. First... What you'd want to use is =indirect() and build a formula that results in the string that points at the folder, file, sheet, location. But the bad thing is that =indirect() won't work if that sending file is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Second... =sumif() will return an error if the workbook is closed. You could replace it with =sumproduct() =sumproduct(--('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22=C6), ('[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22)) But you'd still may have to overcome the closed workbook problem. ps. You can't use the entire column in the =sumproduct() formula unless you're using xl2007. Nancy Taylor wrote: I hope this is an easy question to answer... I am attempting to use a SUMIF to pull in numbers from a separate timesheet and I would like to build part of the external reference workbook name from a cell instead of hard-coding it. For example: =SUMIF('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22,C6,'[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22) I have a cell with the resource name and I would like to plug in the name from that cell for ââ¬ÅFirstName_LastNameââ¬Â but I canââ¬â¢t seem to get that to work. Any thoughts? -- Dave Peterson -- Dave Peterson |
Building Dynamic SUMIF statement
Nancy is using =indirect.ext() from Laurent Longre's addin (morefunc.xll).
Don Guillett wrote: As Dave and I both said, INDIRECT does NOT work with closed workbooks. !!! -- Don Guillett Microsoft MVP Excel SalesAid Software "Nancy Taylor" wrote in message ... Not sure if my last post worked so trying again... Here's my formula: =SUMPRODUCT(--(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]Weekly Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!$L:$L"))) B2 = 09122009 D6 = Nancy Taylor C6 = Project and phase name I need to total column N = timesheet project and phase names column L = timesheet hours It seems to work if I have the detail spreadsheets open before I open the summary spreadsheet but if the detail spreadsheets are closed, the #REF! comes up. If I then open the detail sheets, the #REF! resolves itself. Make any sense to anyone? "Dave Peterson" wrote: For me, it's more trial and error. If you can't get it working, share the formula you used. (I'd get the simple =indirect.ext() working first.) And share the values in each of the cells that the formula refers to. I'm sure you'll get some suggestions. Nancy Taylor wrote: Hi Dave, Well, I have gone through and replaced my sumifs with sumproducts and downloaded the morefunc so that I can take advantage of the indirect.ext, but my formulas still give me a #REF! if the associated spreadsheets are closed. Is there a trick to getting the indirect.ext to work? Nancy "Dave Peterson" wrote: You've got a couple of obstacles to overcome. First... What you'd want to use is =indirect() and build a formula that results in the string that points at the folder, file, sheet, location. But the bad thing is that =indirect() won't work if that sending file is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Second... =sumif() will return an error if the workbook is closed. You could replace it with =sumproduct() =sumproduct(--('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22=C6), ('[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22)) But you'd still may have to overcome the closed workbook problem. ps. You can't use the entire column in the =sumproduct() formula unless you're using xl2007. Nancy Taylor wrote: I hope this is an easy question to answer... I am attempting to use a SUMIF to pull in numbers from a separate timesheet and I would like to build part of the external reference workbook name from a cell instead of hard-coding it. For example: =SUMIF('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22,C6,'[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22) I have a cell with the resource name and I would like to plug in the name from that cell for ââ¬ÅFirstName_LastNameââ¬Â but I canââ¬â¢t seem to get that to work. Any thoughts? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Building Dynamic SUMIF statement
You need to include the drive and folder, too. Otherwise, how would
=indirect.ext() know which drive/folder to look into. ps. If that 09122009 is a date that's formatted as mmddyyyy (or ddmmyyyy), you'll want to use: ...&text($b$2,"mmddyyyy")&... or if it's a plain old number that's formatted to show leading 0's: ...&text($b$2,"00000000")&... ======== pps. Remember that if you have to share this workbook with people not using xl2007, then you won't want to use the entire column. (that only works in xl2007.) Nancy Taylor wrote: Not sure if my last post worked so trying again... Here's my formula: =SUMPRODUCT(--(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]Weekly Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!$L:$L"))) B2 = 09122009 D6 = Nancy Taylor C6 = Project and phase name I need to total column N = timesheet project and phase names column L = timesheet hours It seems to work if I have the detail spreadsheets open before I open the summary spreadsheet but if the detail spreadsheets are closed, the #REF! comes up. If I then open the detail sheets, the #REF! resolves itself. Make any sense to anyone? "Dave Peterson" wrote: For me, it's more trial and error. If you can't get it working, share the formula you used. (I'd get the simple =indirect.ext() working first.) And share the values in each of the cells that the formula refers to. I'm sure you'll get some suggestions. Nancy Taylor wrote: Hi Dave, Well, I have gone through and replaced my sumifs with sumproducts and downloaded the morefunc so that I can take advantage of the indirect.ext, but my formulas still give me a #REF! if the associated spreadsheets are closed. Is there a trick to getting the indirect.ext to work? Nancy "Dave Peterson" wrote: You've got a couple of obstacles to overcome. First... What you'd want to use is =indirect() and build a formula that results in the string that points at the folder, file, sheet, location. But the bad thing is that =indirect() won't work if that sending file is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Second... =sumif() will return an error if the workbook is closed. You could replace it with =sumproduct() =sumproduct(--('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22=C6), ('[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22)) But you'd still may have to overcome the closed workbook problem. ps. You can't use the entire column in the =sumproduct() formula unless you're using xl2007. Nancy Taylor wrote: I hope this is an easy question to answer... I am attempting to use a SUMIF to pull in numbers from a separate timesheet and I would like to build part of the external reference workbook name from a cell instead of hard-coding it. For example: =SUMIF('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22,C6,'[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22) I have a cell with the resource name and I would like to plug in the name from that cell for ââ¬ÅFirstName_LastNameââ¬Â but I canââ¬â¢t seem to get that to work. Any thoughts? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Building Dynamic SUMIF statement
ppps. Remember that if you do share the workbook with that formula with others,
then the recipients will have to have that MoreFunc.xll addin, too. Dave Peterson wrote: You need to include the drive and folder, too. Otherwise, how would =indirect.ext() know which drive/folder to look into. ps. If that 09122009 is a date that's formatted as mmddyyyy (or ddmmyyyy), you'll want to use: ...&text($b$2,"mmddyyyy")&... or if it's a plain old number that's formatted to show leading 0's: ...&text($b$2,"00000000")&... ======== pps. Remember that if you have to share this workbook with people not using xl2007, then you won't want to use the entire column. (that only works in xl2007.) Nancy Taylor wrote: Not sure if my last post worked so trying again... Here's my formula: =SUMPRODUCT(--(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]Weekly Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!$L:$L"))) B2 = 09122009 D6 = Nancy Taylor C6 = Project and phase name I need to total column N = timesheet project and phase names column L = timesheet hours It seems to work if I have the detail spreadsheets open before I open the summary spreadsheet but if the detail spreadsheets are closed, the #REF! comes up. If I then open the detail sheets, the #REF! resolves itself. Make any sense to anyone? "Dave Peterson" wrote: For me, it's more trial and error. If you can't get it working, share the formula you used. (I'd get the simple =indirect.ext() working first.) And share the values in each of the cells that the formula refers to. I'm sure you'll get some suggestions. Nancy Taylor wrote: Hi Dave, Well, I have gone through and replaced my sumifs with sumproducts and downloaded the morefunc so that I can take advantage of the indirect.ext, but my formulas still give me a #REF! if the associated spreadsheets are closed. Is there a trick to getting the indirect.ext to work? Nancy "Dave Peterson" wrote: You've got a couple of obstacles to overcome. First... What you'd want to use is =indirect() and build a formula that results in the string that points at the folder, file, sheet, location. But the bad thing is that =indirect() won't work if that sending file is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Second... =sumif() will return an error if the workbook is closed. You could replace it with =sumproduct() =sumproduct(--('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22=C6), ('[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22)) But you'd still may have to overcome the closed workbook problem. ps. You can't use the entire column in the =sumproduct() formula unless you're using xl2007. Nancy Taylor wrote: I hope this is an easy question to answer... I am attempting to use a SUMIF to pull in numbers from a separate timesheet and I would like to build part of the external reference workbook name from a cell instead of hard-coding it. For example: =SUMIF('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22,C6,'[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22) I have a cell with the resource name and I would like to plug in the name from that cell for ââ¬ÅFirstName_LastNameââ¬Â but I canââ¬â¢t seem to get that to work. Any thoughts? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Building Dynamic SUMIF statement
Hi Dave,
thanks for the update. A couple of comments... - I will deifnitely have everyone using these spreadsheets install the add in - no problem. - The detail spreadsheets will be in the same folder as the summary spreadsheet. Since I don't dictate where these go, I was expecting to not have to specify drives, etc if they will reside together. True? - all users have xl2007 so I'm ok there. - the date format is really a text field that is part of the filename. I don't think I need to handle any formatting there. I am not seeing the benefit of the indirect.ext at this point though. Unless I open the detail spreadsheets, the indirect.ext doesn't seem to resolve the references in the closed spreadsheets and shows only #REF!. I have to open the detail files to get the reference to resolve itself still?? "Dave Peterson" wrote: ppps. Remember that if you do share the workbook with that formula with others, then the recipients will have to have that MoreFunc.xll addin, too. Dave Peterson wrote: You need to include the drive and folder, too. Otherwise, how would =indirect.ext() know which drive/folder to look into. ps. If that 09122009 is a date that's formatted as mmddyyyy (or ddmmyyyy), you'll want to use: ...&text($b$2,"mmddyyyy")&... or if it's a plain old number that's formatted to show leading 0's: ...&text($b$2,"00000000")&... ======== pps. Remember that if you have to share this workbook with people not using xl2007, then you won't want to use the entire column. (that only works in xl2007.) Nancy Taylor wrote: Not sure if my last post worked so trying again... Here's my formula: =SUMPRODUCT(--(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]Weekly Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!$L:$L"))) B2 = 09122009 D6 = Nancy Taylor C6 = Project and phase name I need to total column N = timesheet project and phase names column L = timesheet hours It seems to work if I have the detail spreadsheets open before I open the summary spreadsheet but if the detail spreadsheets are closed, the #REF! comes up. If I then open the detail sheets, the #REF! resolves itself. Make any sense to anyone? "Dave Peterson" wrote: For me, it's more trial and error. If you can't get it working, share the formula you used. (I'd get the simple =indirect.ext() working first.) And share the values in each of the cells that the formula refers to. I'm sure you'll get some suggestions. Nancy Taylor wrote: Hi Dave, Well, I have gone through and replaced my sumifs with sumproducts and downloaded the morefunc so that I can take advantage of the indirect.ext, but my formulas still give me a #REF! if the associated spreadsheets are closed. Is there a trick to getting the indirect.ext to work? Nancy "Dave Peterson" wrote: You've got a couple of obstacles to overcome. First... What you'd want to use is =indirect() and build a formula that results in the string that points at the folder, file, sheet, location. But the bad thing is that =indirect() won't work if that sending file is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Second... =sumif() will return an error if the workbook is closed. You could replace it with =sumproduct() =sumproduct(--('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22=C6), ('[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22)) But you'd still may have to overcome the closed workbook problem. ps. You can't use the entire column in the =sumproduct() formula unless you're using xl2007. Nancy Taylor wrote: I hope this is an easy question to answer... I am attempting to use a SUMIF to pull in numbers from a separate timesheet and I would like to build part of the external reference workbook name from a cell instead of hard-coding it. For example: =SUMIF('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22,C6,'[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22) I have a cell with the resource name and I would like to plug in the name from that cell for âââšÂ¬ÃâœFirstName_LastN ameâââšÂ¬Ã but I canâââšÂ¬Ã¢âžÂ¢t seem to get that to work. Any thoughts? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Building Dynamic SUMIF statement
I believe I may have found information that explains what I am seeing. I
found a posting that says the indirect.ext only works for a single cell reference. Since I am trying to use it to retrieve a range of cells from a closed workbook, I don't think it will work for me? "Nancy Taylor" wrote: Hi Dave, thanks for the update. A couple of comments... - I will deifnitely have everyone using these spreadsheets install the add in - no problem. - The detail spreadsheets will be in the same folder as the summary spreadsheet. Since I don't dictate where these go, I was expecting to not have to specify drives, etc if they will reside together. True? - all users have xl2007 so I'm ok there. - the date format is really a text field that is part of the filename. I don't think I need to handle any formatting there. I am not seeing the benefit of the indirect.ext at this point though. Unless I open the detail spreadsheets, the indirect.ext doesn't seem to resolve the references in the closed spreadsheets and shows only #REF!. I have to open the detail files to get the reference to resolve itself still?? "Dave Peterson" wrote: ppps. Remember that if you do share the workbook with that formula with others, then the recipients will have to have that MoreFunc.xll addin, too. Dave Peterson wrote: You need to include the drive and folder, too. Otherwise, how would =indirect.ext() know which drive/folder to look into. ps. If that 09122009 is a date that's formatted as mmddyyyy (or ddmmyyyy), you'll want to use: ...&text($b$2,"mmddyyyy")&... or if it's a plain old number that's formatted to show leading 0's: ...&text($b$2,"00000000")&... ======== pps. Remember that if you have to share this workbook with people not using xl2007, then you won't want to use the entire column. (that only works in xl2007.) Nancy Taylor wrote: Not sure if my last post worked so trying again... Here's my formula: =SUMPRODUCT(--(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]Weekly Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!$L:$L"))) B2 = 09122009 D6 = Nancy Taylor C6 = Project and phase name I need to total column N = timesheet project and phase names column L = timesheet hours It seems to work if I have the detail spreadsheets open before I open the summary spreadsheet but if the detail spreadsheets are closed, the #REF! comes up. If I then open the detail sheets, the #REF! resolves itself. Make any sense to anyone? "Dave Peterson" wrote: For me, it's more trial and error. If you can't get it working, share the formula you used. (I'd get the simple =indirect.ext() working first.) And share the values in each of the cells that the formula refers to. I'm sure you'll get some suggestions. Nancy Taylor wrote: Hi Dave, Well, I have gone through and replaced my sumifs with sumproducts and downloaded the morefunc so that I can take advantage of the indirect.ext, but my formulas still give me a #REF! if the associated spreadsheets are closed. Is there a trick to getting the indirect.ext to work? Nancy "Dave Peterson" wrote: You've got a couple of obstacles to overcome. First... What you'd want to use is =indirect() and build a formula that results in the string that points at the folder, file, sheet, location. But the bad thing is that =indirect() won't work if that sending file is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Second... =sumif() will return an error if the workbook is closed. You could replace it with =sumproduct() =sumproduct(--('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22=C6), ('[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22)) But you'd still may have to overcome the closed workbook problem. ps. You can't use the entire column in the =sumproduct() formula unless you're using xl2007. Nancy Taylor wrote: I hope this is an easy question to answer... I am attempting to use a SUMIF to pull in numbers from a separate timesheet and I would like to build part of the external reference workbook name from a cell instead of hard-coding it. For example: =SUMIF('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22,C6,'[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22) I have a cell with the resource name and I would like to plug in the name from that cell for âââšÂ¬ÃâœFirstName_LastN ameâââšÂ¬Ã but I canâââšÂ¬Ã¢âžÂ¢t seem to get that to work. Any thoughts? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Building Dynamic SUMIF statement
First, I don't use Laurent's addin very often (hardly ever).
But it sure makes sense that you'll have to specify the drive and folder for it to work (if those "sending" workbooks are closed). And I bet if you try your formula with the drive/folder specified, you'll find that your =sumproduct() works ok. The problem I've had with using entire columns (in xl2003!) is that I sometimes get the message "unable to complete the task with available resources" (or something like that). I have to adjust the ranges to make my non-indirect formulas work. Nancy Taylor wrote: I believe I may have found information that explains what I am seeing. I found a posting that says the indirect.ext only works for a single cell reference. Since I am trying to use it to retrieve a range of cells from a closed workbook, I don't think it will work for me? "Nancy Taylor" wrote: Hi Dave, thanks for the update. A couple of comments... - I will deifnitely have everyone using these spreadsheets install the add in - no problem. - The detail spreadsheets will be in the same folder as the summary spreadsheet. Since I don't dictate where these go, I was expecting to not have to specify drives, etc if they will reside together. True? - all users have xl2007 so I'm ok there. - the date format is really a text field that is part of the filename. I don't think I need to handle any formatting there. I am not seeing the benefit of the indirect.ext at this point though. Unless I open the detail spreadsheets, the indirect.ext doesn't seem to resolve the references in the closed spreadsheets and shows only #REF!. I have to open the detail files to get the reference to resolve itself still?? "Dave Peterson" wrote: ppps. Remember that if you do share the workbook with that formula with others, then the recipients will have to have that MoreFunc.xll addin, too. Dave Peterson wrote: You need to include the drive and folder, too. Otherwise, how would =indirect.ext() know which drive/folder to look into. ps. If that 09122009 is a date that's formatted as mmddyyyy (or ddmmyyyy), you'll want to use: ...&text($b$2,"mmddyyyy")&... or if it's a plain old number that's formatted to show leading 0's: ...&text($b$2,"00000000")&... ======== pps. Remember that if you have to share this workbook with people not using xl2007, then you won't want to use the entire column. (that only works in xl2007.) Nancy Taylor wrote: Not sure if my last post worked so trying again... Here's my formula: =SUMPRODUCT(--(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]Weekly Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!$L:$L"))) B2 = 09122009 D6 = Nancy Taylor C6 = Project and phase name I need to total column N = timesheet project and phase names column L = timesheet hours It seems to work if I have the detail spreadsheets open before I open the summary spreadsheet but if the detail spreadsheets are closed, the #REF! comes up. If I then open the detail sheets, the #REF! resolves itself. Make any sense to anyone? "Dave Peterson" wrote: For me, it's more trial and error. If you can't get it working, share the formula you used. (I'd get the simple =indirect.ext() working first.) And share the values in each of the cells that the formula refers to. I'm sure you'll get some suggestions. Nancy Taylor wrote: Hi Dave, Well, I have gone through and replaced my sumifs with sumproducts and downloaded the morefunc so that I can take advantage of the indirect.ext, but my formulas still give me a #REF! if the associated spreadsheets are closed. Is there a trick to getting the indirect.ext to work? Nancy "Dave Peterson" wrote: You've got a couple of obstacles to overcome. First... What you'd want to use is =indirect() and build a formula that results in the string that points at the folder, file, sheet, location. But the bad thing is that =indirect() won't work if that sending file is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Second... =sumif() will return an error if the workbook is closed. You could replace it with =sumproduct() =sumproduct(--('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22=C6), ('[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22)) But you'd still may have to overcome the closed workbook problem. ps. You can't use the entire column in the =sumproduct() formula unless you're using xl2007. Nancy Taylor wrote: I hope this is an easy question to answer... I am attempting to use a SUMIF to pull in numbers from a separate timesheet and I would like to build part of the external reference workbook name from a cell instead of hard-coding it. For example: =SUMIF('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22,C6,'[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22) I have a cell with the resource name and I would like to plug in the name from that cell for âââšÂ¬ÃâœFirstName_LastN ameâââšÂ¬Ã but I canâââšÂ¬Ã¢âžÂ¢t seem to get that to work. Any thoughts? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Building Dynamic SUMIF statement
Well, I included the full path to the file but still no dice:
=SUMPRODUCT(--(INDIRECT.EXT("'c:\temp\["&$B$2&"_"&D6&".xlsm]Weekly Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'c:\temp\["&$B$2&"_"&D6&".xlsm]weekly timesheet'!$L:$L"))) I tried with and without the "\". I'm stumped... "Nancy Taylor" wrote: Hi Dave, thanks for the update. A couple of comments... - I will deifnitely have everyone using these spreadsheets install the add in - no problem. - The detail spreadsheets will be in the same folder as the summary spreadsheet. Since I don't dictate where these go, I was expecting to not have to specify drives, etc if they will reside together. True? - all users have xl2007 so I'm ok there. - the date format is really a text field that is part of the filename. I don't think I need to handle any formatting there. I am not seeing the benefit of the indirect.ext at this point though. Unless I open the detail spreadsheets, the indirect.ext doesn't seem to resolve the references in the closed spreadsheets and shows only #REF!. I have to open the detail files to get the reference to resolve itself still?? "Dave Peterson" wrote: ppps. Remember that if you do share the workbook with that formula with others, then the recipients will have to have that MoreFunc.xll addin, too. Dave Peterson wrote: You need to include the drive and folder, too. Otherwise, how would =indirect.ext() know which drive/folder to look into. ps. If that 09122009 is a date that's formatted as mmddyyyy (or ddmmyyyy), you'll want to use: ...&text($b$2,"mmddyyyy")&... or if it's a plain old number that's formatted to show leading 0's: ...&text($b$2,"00000000")&... ======== pps. Remember that if you have to share this workbook with people not using xl2007, then you won't want to use the entire column. (that only works in xl2007.) Nancy Taylor wrote: Not sure if my last post worked so trying again... Here's my formula: =SUMPRODUCT(--(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]Weekly Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!$L:$L"))) B2 = 09122009 D6 = Nancy Taylor C6 = Project and phase name I need to total column N = timesheet project and phase names column L = timesheet hours It seems to work if I have the detail spreadsheets open before I open the summary spreadsheet but if the detail spreadsheets are closed, the #REF! comes up. If I then open the detail sheets, the #REF! resolves itself. Make any sense to anyone? "Dave Peterson" wrote: For me, it's more trial and error. If you can't get it working, share the formula you used. (I'd get the simple =indirect.ext() working first.) And share the values in each of the cells that the formula refers to. I'm sure you'll get some suggestions. Nancy Taylor wrote: Hi Dave, Well, I have gone through and replaced my sumifs with sumproducts and downloaded the morefunc so that I can take advantage of the indirect.ext, but my formulas still give me a #REF! if the associated spreadsheets are closed. Is there a trick to getting the indirect.ext to work? Nancy "Dave Peterson" wrote: You've got a couple of obstacles to overcome. First... What you'd want to use is =indirect() and build a formula that results in the string that points at the folder, file, sheet, location. But the bad thing is that =indirect() won't work if that sending file is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Second... =sumif() will return an error if the workbook is closed. You could replace it with =sumproduct() =sumproduct(--('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22=C6), ('[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22)) But you'd still may have to overcome the closed workbook problem. ps. You can't use the entire column in the =sumproduct() formula unless you're using xl2007. Nancy Taylor wrote: I hope this is an easy question to answer... I am attempting to use a SUMIF to pull in numbers from a separate timesheet and I would like to build part of the external reference workbook name from a cell instead of hard-coding it. For example: =SUMIF('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22,C6,'[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22) I have a cell with the resource name and I would like to plug in the name from that cell for âââšÂ¬ÃâœFirstName_LastN ameâââšÂ¬Ã but I canâââšÂ¬Ã¢âžÂ¢t seem to get that to work. Any thoughts? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 07:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com