Spin Box Date and Sumproduct

[JPDS]

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

 

[Luke M]

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")))

 

[Stefi]

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,6000))

--
Regards!
Stefi



„JPDS†ezt írta:

 

[JPDS]

Fantastic, thats the finishing touch I needed!

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!*