Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 29
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,722
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,646
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 29
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Spin box increase value only Mark N Excel Discussion (Misc queries) 1 August 27th 09 03:51 PM
Make a chart spin??? Matthew Wells Charts and Charting in Excel 7 July 13th 08 04:49 PM
Spin button very slow [email protected] Excel Discussion (Misc queries) 0 June 12th 07 03:15 PM
spin boxes PĂ© Excel Worksheet Functions 1 October 14th 06 10:03 PM
spin button value tina Excel Discussion (Misc queries) 2 March 17th 05 03:11 PM


All times are GMT +1. The time now is 12:07 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"