Combo & List Box Needed to set Calendar Quarter & Ye

[psulion01]

I need some help... I need to define a specific calendar quarter and year, and want to do so using a combo box for the quarter (e.g. 1st Quarter, 2nd Quarter, 3rd Quarter, 4th Quarter) and a list box for the year (so the user can select any year desired).

The selections in each item should then somehow define the quarter's date range so I can use it in my SUMPRODUCT calculations.

Example:

User selects '1st Quarter' from the Combo Box
User selects '2004' from the List Box

CurrentQuarter range is somehow defined to equal 1/1/04 through 3/31/04.

If this isn't possible, then perhaps two ranges can be defined based on the user's selections:

QtrStart is set to 1/1/04
QtrEnd is set to 3/31/04


Thoughts? I'm totally stumped with this one!

Thanks!

 

[Datsmart]

Here are some formula to gather the Dates and Quarter Number you need. I will let you sort out your combo box portion.

Excel Workbook
	A	B	C	D
1	Date	Start of Quarter	End of Quarter	Quarter #
2	02/02/04	01/01/04	03/31/04	1
Sheet

Hope this helps you.

 

[psulion01]

Great...thanks!

However, is there some way to accomplish the same sort of task without needing to run the calculations on every line on my data sheet? I don't want to touch the master sheet, as I want others to be able to import new data without worrying about any formulas, etc. Ideally, all of the calculations can be housed on one sheet.

Thoughts?

 

[Datsmart]

From what you have explained, I have no clue how to help you further.

Going to need to know how your data is laid out.
Could you post a small portion?
Excel Jeanie or the HTML maker produce readable views of your worksheet in the forum.

 

[psulion01]

Sure thing John...

Here's a sample of the data sheet:

EFS Regional Scorecard.xls
	A	B	C	D
1	TransactionType	DateApproved	MarketValue	RegionName
2	10b5-1Plan-Sale	6/27/2007	$546,540	East
3	10b5-1Plan-Sale	2/22/2006	$6,540	East
4	10b5-1Plan-Sale	11/27/2005	$654,065	West
5	LegendRemoval	5/31/2007	$23,135	North
6	LegendRemoval	4/23/2006	$6,408,004	South
7	Loan	4/19/2004	$8,068	West
8	PublicSale	9/4/2005	$325,460	North
9	PublicSale	6/19/2007	$84,684	East
10	PublicSale	5/25/2004	$564,068	West
11	StockOptionExercise	5/25/2005	$8,400	East
12	StockOptionExercise	9/11/2003	$84,006	East
13	StockOptionExercise	6/12/2007	$6,848	West
14	StockOptionExercise	12/4/2007	$68,468	South
15	StockOptionExercise	1/3/2005	$580,460	South
16	StockOptionExercise	1/21/2006	$584,064	West
Sheet1

Here is the report with my sumproduct formulas:

EFS Regional Scorecard.xls
	B	C	D	E	F
12		Q2'07	Q2'07	Q2'06	Q2'06
13	TransactionType	Count	Value	Count	Value
14	10b5-1Plan-Sale	2	$2,326,832	0	$-
15	PublicSale	29	$15,484,125	34	$4,704,049
16	StockOptionExercise	6	$11,319,797	13	$5,369,406
17	Loan	1	$8,074,512	0	$-
18	LegendRemoval	11	$1,753,847	12	$3,459,514
Scorecard

I currently have a combo box that allows the user to set the 'RegionID' value. All ranges named data2007_xxxxx correspond to the columns of data in my master data sheet (first table above). Essentially I want a combo/list box that allows the user to set the dates to filter by, which are currently hardcoded in the formulas in cell D14.

Hopefully this makes my request a bit more clear...

Thanks!
Mike

 

[Datsmart]

Can't you also use your ComboBox to set Named Ranges for your dates to filter by?
Or you could use a Spinner button to change dates in a couple of cells and reference those cells in your formula.

 

[psulion01]

exactly what I want to do, but don't know how to set the combo box to select the quarter (based on month -- Jan-Mar), and then the spinner to set the year. Seems that excel is forcing the year on me when i try to establish the date. I don't know how to set just Jan 1 (with no year)... It always seems to see the value as Jan 1, 2007.

Maybe I need to force the combo box to use 1900 as the year, and then using the value selected in the spinner I can add X years to 1900, thus giving me the desired quarter and year.

Example: User selects 1st Quarter from a combo box. This sets a date of 1/1/1900 in a cell. Then the spinner is set to 2005. Another cell would calculate the adjusted date by taking 1/1/1900 and adding 105 to the year, yielding 1/1/2005.

Thoughts?

 

[Datsmart]

You could setup two Spinners and let your formulae reference the Start and End dates.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Quarter #	Year	Start of Quarter	End of Quarter		Spinner for Q#		Spinner for Year
2	2	2005	04/01/05	06/30/05
3						Spinner		Spinner
4						for Cell		for Cell
5						A2		B2
6
Sheet

 

[psulion01]

Great! Works like a charm!

I ended up using a combo box for the year rather than a spinner (didn't like how the spinner looked on the sheet.

Last question on this one... Is it possible to populate the combo box/spinner with the years WITHOUT needing to list each year in a column somewhere? Right now I have a Controls sheet with a column for year, e.g. 1999, 2000, 2001, 2002 etc, and my combo box is looking here for its source. Any way to simplify this so it just knows that the source data should be years?

Awesome stuff... thanks again

 

[Datsmart]

I would suggest setting up your ComboBox source list like this:

Excel Workbook
	B
8	Years
9	1999
10	2000
11	2001
12	2002
13	2003
14	2004
15	2005
16	2006
17	2007
Sheet

Then it will always include an up to date list.

It is possible to code your combobox to build its' own list, but the code would be dependent on which type of combobox you are using, Forms or Control Tool Bar. Maybe someone has some ready code to do that, I don't.