Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spin Box Date and Sumproduct
Is it possible to use a date (created via an INDEX calculation) within a
SUMPRODUCT calculation? I use a SUMPRODUCT calculation to generate headcount figures off monthly master sheets as per the following: =(SUMPRODUCT(--(Apr09!$O$1:$O$6000="XN01"),(Apr09!$CI$1:$CI$6000) )). However, I would like to now change the date source of the data based on a month name generated by a Spin Box so that when the month changes fropm Apr09 to May09, the SUMPRODUCT calculation refers to the sheet of May instead. So how do you join the '$O$1:$O$6000' part to the cell containing the month date? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spin Box Date and Sumproduct
If all the data was on one sheet, you could use the INDEX function with the
MATCH function to have the data shift. Since you want to change sheets, this won't work. However, you can use the INDIRECT function (see XL help file for more detail). If the Spin Box is changing the Month name (and not the year) and this info appears in cell A1, formula would be: =SUMPRODUCT(--(INDIRECT(A1&"09!$O$1:$O$6000")="XN01"),(INDIRECT( A1&"09!$CI$1:$CI$6000"))) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JPDS" wrote: Is it possible to use a date (created via an INDEX calculation) within a SUMPRODUCT calculation? I use a SUMPRODUCT calculation to generate headcount figures off monthly master sheets as per the following: =(SUMPRODUCT(--(Apr09!$O$1:$O$6000="XN01"),(Apr09!$CI$1:$CI$6000) )). However, I would like to now change the date source of the data based on a month name generated by a Spin Box so that when the month changes fropm Apr09 to May09, the SUMPRODUCT calculation refers to the sheet of May instead. So how do you join the '$O$1:$O$6000' part to the cell containing the month date? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spin Box Date and Sumproduct
If month name is in A1 then
=SUMPRODUCT(--(OFFSET(INDIRECT(ADDRESS(1,15,1,1,A1)),0,0,6000)=" XN01"),OFFSET(INDIRECT(ADDRESS(1,3,1,1,A1)),0,0,60 00)) -- Regards! Stefi €žJPDS€ť ezt Ă*rta: Is it possible to use a date (created via an INDEX calculation) within a SUMPRODUCT calculation? I use a SUMPRODUCT calculation to generate headcount figures off monthly master sheets as per the following: =(SUMPRODUCT(--(Apr09!$O$1:$O$6000="XN01"),(Apr09!$CI$1:$CI$6000) )). However, I would like to now change the date source of the data based on a month name generated by a Spin Box so that when the month changes fropm Apr09 to May09, the SUMPRODUCT calculation refers to the sheet of May instead. So how do you join the '$O$1:$O$6000' part to the cell containing the month date? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spin Box Date and Sumproduct
Fantastic, thats the finishing touch I needed!
"Luke M" wrote: If all the data was on one sheet, you could use the INDEX function with the MATCH function to have the data shift. Since you want to change sheets, this won't work. However, you can use the INDIRECT function (see XL help file for more detail). If the Spin Box is changing the Month name (and not the year) and this info appears in cell A1, formula would be: =SUMPRODUCT(--(INDIRECT(A1&"09!$O$1:$O$6000")="XN01"),(INDIRECT( A1&"09!$CI$1:$CI$6000"))) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JPDS" wrote: Is it possible to use a date (created via an INDEX calculation) within a SUMPRODUCT calculation? I use a SUMPRODUCT calculation to generate headcount figures off monthly master sheets as per the following: =(SUMPRODUCT(--(Apr09!$O$1:$O$6000="XN01"),(Apr09!$CI$1:$CI$6000) )). However, I would like to now change the date source of the data based on a month name generated by a Spin Box so that when the month changes fropm Apr09 to May09, the SUMPRODUCT calculation refers to the sheet of May instead. So how do you join the '$O$1:$O$6000' part to the cell containing the month date? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spin box increase value only | Excel Discussion (Misc queries) | |||
Make a chart spin??? | Charts and Charting in Excel | |||
Spin button very slow | Excel Discussion (Misc queries) | |||
spin boxes | Excel Worksheet Functions | |||
spin button value | Excel Discussion (Misc queries) |