|
Indirect and Sumif Syntax Problems
I'm trying to improve the following line of code so that I can
substitute different file names when necessary, but can't get the syntax right with the Indirect function. =SUMIF('[1 Feb 06 - 30 Apr 06.xls]Sheet1'!$E:$E,B21,'[1 Feb 06 - 30 Apr 06.xls]Sheet1'!$AA:$AA) The spreadsheet that this line is in runs to roughly 100 lines, and a similar formula is used in two other cell locations on each row. The spreadsheet that it references (1 Feb 06 - 30 Apr 06.xls in this instance) runs to roughly 13,500 lines. If I use a different spreadsheet (e.g. 1 May 06 - 31 Jul 06.xls), then it means changing the above line roughly 300 times for 600 occurences. What I have been trying to do insert '1 May 06 - 31 Jul 06.xls' into one cell and then use the indirect function within the sumif. In this way I would only have to make one single change to the entire spreadsheet. Any help would be appreciated. |
Indirect and Sumif Syntax Problems
Dave
You're a genius. That will save me hours of work. I spent about two hours yesterday trying different ways of writing that formula, but just couldn't get it sorted. Thanks very much for the help. Dave Peterson wrote: Maybe you can let excel do the work. Edit|links|change source. Be aware that the "sending" workbook has to be open for =Sumif() to work. And if you decide to use =indirect(), then the other workbook(s) have to be open, too. I put the file name of the open workbook in A1 and then used this formula: =SUMIF(INDIRECT("'["&A1&"]Sheet1'!$E:$E"),B21, INDIRECT("'["&A1&"]Sheet1'!$aa:$aa")) A1 contained this: 1 Feb 06 - 30 Apr 06.xls Nothing else. Remember that the sending workbook has to be open for =sumif() to work and for =indirect() to work--a double whammy! =sumif() has an equivalent =sumproduct() expression (not using the whole column. and Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. (I've never used it, though.) You may be able to use a combination of both these to keep the sending workbook closed. wrote: I'm trying to improve the following line of code so that I can substitute different file names when necessary, but can't get the syntax right with the Indirect function. =SUMIF('[1 Feb 06 - 30 Apr 06.xls]Sheet1'!$E:$E,B21,'[1 Feb 06 - 30 Apr 06.xls]Sheet1'!$AA:$AA) The spreadsheet that this line is in runs to roughly 100 lines, and a similar formula is used in two other cell locations on each row. The spreadsheet that it references (1 Feb 06 - 30 Apr 06.xls in this instance) runs to roughly 13,500 lines. If I use a different spreadsheet (e.g. 1 May 06 - 31 Jul 06.xls), then it means changing the above line roughly 300 times for 600 occurences. What I have been trying to do insert '1 May 06 - 31 Jul 06.xls' into one cell and then use the indirect function within the sumif. In this way I would only have to make one single change to the entire spreadsheet. Any help would be appreciated. -- Dave Peterson |
Indirect and Sumif Syntax Problems
Sometimes, it's just setting up a "normal" formula and doing trial and error to
get the =indirect() to match that formula. (Well, for me, it's trial and error!) wrote: Dave You're a genius. That will save me hours of work. I spent about two hours yesterday trying different ways of writing that formula, but just couldn't get it sorted. Thanks very much for the help. Dave Peterson wrote: Maybe you can let excel do the work. Edit|links|change source. Be aware that the "sending" workbook has to be open for =Sumif() to work. And if you decide to use =indirect(), then the other workbook(s) have to be open, too. I put the file name of the open workbook in A1 and then used this formula: =SUMIF(INDIRECT("'["&A1&"]Sheet1'!$E:$E"),B21, INDIRECT("'["&A1&"]Sheet1'!$aa:$aa")) A1 contained this: 1 Feb 06 - 30 Apr 06.xls Nothing else. Remember that the sending workbook has to be open for =sumif() to work and for =indirect() to work--a double whammy! =sumif() has an equivalent =sumproduct() expression (not using the whole column. and Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. (I've never used it, though.) You may be able to use a combination of both these to keep the sending workbook closed. wrote: I'm trying to improve the following line of code so that I can substitute different file names when necessary, but can't get the syntax right with the Indirect function. =SUMIF('[1 Feb 06 - 30 Apr 06.xls]Sheet1'!$E:$E,B21,'[1 Feb 06 - 30 Apr 06.xls]Sheet1'!$AA:$AA) The spreadsheet that this line is in runs to roughly 100 lines, and a similar formula is used in two other cell locations on each row. The spreadsheet that it references (1 Feb 06 - 30 Apr 06.xls in this instance) runs to roughly 13,500 lines. If I use a different spreadsheet (e.g. 1 May 06 - 31 Jul 06.xls), then it means changing the above line roughly 300 times for 600 occurences. What I have been trying to do insert '1 May 06 - 31 Jul 06.xls' into one cell and then use the indirect function within the sumif. In this way I would only have to make one single change to the entire spreadsheet. Any help would be appreciated. -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 06:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com