Walking Shorts
New Member
- Joined
- Nov 23, 2005
- Messages
- 17
I need to make a formula to reference data in cells from various worksheets and total that data which aslo includes the INDIRECT.EXT function and will also ignore if certian workbooks are not avaiable. I'll try to explain a little better-
I am making a workbook to gather data from other workbooks, each workbook representing stats collected for certian months and certian years. For example, the workbook named dejan05.xls contains the data(d) entered(e) from January(jan) 2005(05), so defeb05.xls would have the data from February 2005 and so forth. The data I need from each workbook is contained on the worksheet title Monthly, and is in cell D6.
the formula also needs to gather the filename based on what year is typed in by the user in cell C1, so if they type in 2004 into cell C1, the data will be gathered from the workbooks that are from that year only (dejan04.xls, defeb04.xls, demar04.xls, etc). This part I don't really need too much help with, I'm just mentioning it so you know why I need the INDIRECT.EXT function in the formula. More or less I've got the basic filenames showing up
in cells F2 to F13 (dejan to dedec) formulated so that they change based on what year is typed into C1, and also include the name of the worksheet I need the data from as well, the code looks like this-
="[dejan"&(RIGHT($C$1,2))&".xls]Monthly'!" - So basically what shows up in the cell (F2 in this example, and assuming 2005 is in cell C1) is [dejan05.xls]Monthly'!
So now I can type in something like =INDIRECT.EXT("'\\Path\Name\"&F2&"D6") and have the data from cell D6 on the sheet titled Monthly from the workbook named dejan05.xls appear. But that's about when I get stuck. I know I could get the total from all the sheets of that year by simply using SUM or even just + to add it all up, but I need the formula to support the fact that I may not have ALL the workbooks avaiable, such as this year since I would have no data for December yet, or half way through next year I may need the year to date totals, or maybe I just have no workbook for March of 2003 because there were no stats to gather for that month. My current formula doesn't work because if the workbook for a certian month is missing, the result that turns up in the cell is #VALUE because it can't find that workbook.
So, I need a formula that will ignore if some workbooks missing and just give me the totals from the workbooks of that year that are available. Any ideas?
I am making a workbook to gather data from other workbooks, each workbook representing stats collected for certian months and certian years. For example, the workbook named dejan05.xls contains the data(d) entered(e) from January(jan) 2005(05), so defeb05.xls would have the data from February 2005 and so forth. The data I need from each workbook is contained on the worksheet title Monthly, and is in cell D6.
the formula also needs to gather the filename based on what year is typed in by the user in cell C1, so if they type in 2004 into cell C1, the data will be gathered from the workbooks that are from that year only (dejan04.xls, defeb04.xls, demar04.xls, etc). This part I don't really need too much help with, I'm just mentioning it so you know why I need the INDIRECT.EXT function in the formula. More or less I've got the basic filenames showing up
in cells F2 to F13 (dejan to dedec) formulated so that they change based on what year is typed into C1, and also include the name of the worksheet I need the data from as well, the code looks like this-
="[dejan"&(RIGHT($C$1,2))&".xls]Monthly'!" - So basically what shows up in the cell (F2 in this example, and assuming 2005 is in cell C1) is [dejan05.xls]Monthly'!
So now I can type in something like =INDIRECT.EXT("'\\Path\Name\"&F2&"D6") and have the data from cell D6 on the sheet titled Monthly from the workbook named dejan05.xls appear. But that's about when I get stuck. I know I could get the total from all the sheets of that year by simply using SUM or even just + to add it all up, but I need the formula to support the fact that I may not have ALL the workbooks avaiable, such as this year since I would have no data for December yet, or half way through next year I may need the year to date totals, or maybe I just have no workbook for March of 2003 because there were no stats to gather for that month. My current formula doesn't work because if the workbook for a certian month is missing, the result that turns up in the cell is #VALUE because it can't find that workbook.
So, I need a formula that will ignore if some workbooks missing and just give me the totals from the workbooks of that year that are available. Any ideas?
