# Combo & List Box Needed to set Calendar Quarter & Ye

#### psulion01

##### Board Regular
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!

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### Datsmart

##### Well-known Member
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
ABCD
1DateStart of QuarterEnd of QuarterQuarter #
202/02/0401/01/0403/31/041
Sheet

Hope this helps you.

#### psulion01

##### Board Regular
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

##### Well-known Member
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

##### Board Regular
Sure thing John...

Here's a sample of the data sheet:
EFS Regional Scorecard.xls
ABCD
1TransactionTypeDateApprovedMarketValueRegionName
210b5-1Plan-Sale6/27/2007\$546,540East
310b5-1Plan-Sale2/22/2006\$6,540East
410b5-1Plan-Sale11/27/2005\$654,065West
5LegendRemoval5/31/2007\$23,135North
6LegendRemoval4/23/2006\$6,408,004South
7Loan4/19/2004\$8,068West
8PublicSale9/4/2005\$325,460North
9PublicSale6/19/2007\$84,684East
10PublicSale5/25/2004\$564,068West
11StockOptionExercise5/25/2005\$8,400East
12StockOptionExercise9/11/2003\$84,006East
13StockOptionExercise6/12/2007\$6,848West
14StockOptionExercise12/4/2007\$68,468South
15StockOptionExercise1/3/2005\$580,460South
16StockOptionExercise1/21/2006\$584,064West
Sheet1

Here is the report with my sumproduct formulas:
EFS Regional Scorecard.xls
BCDEF
12Q2'07Q2'07Q2'06Q2'06
13TransactionTypeCountValueCountValue
1410b5-1Plan-Sale2\$2,326,8320\$-
15PublicSale29\$15,484,12534\$4,704,049
16StockOptionExercise6\$11,319,79713\$5,369,406
17Loan1\$8,074,5120\$-
18LegendRemoval11\$1,753,84712\$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

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
You could setup two Spinners and let your formulae reference the Start and End dates.
Excel Workbook
ABCDEFGHI
1Quarter #YearStart of QuarterEnd of QuarterSpinner for Q#Spinner for Year
22200504/01/0506/30/05
3SpinnerSpinner
4for Cellfor Cell
5A2B2
6
Sheet

#### psulion01

##### Board Regular
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

##### Well-known Member
I would suggest setting up your ComboBox source list like this:
Excel Workbook
B
8Years
91999
102000
112001
122002
132003
142004
152005
162006
172007
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.

Replies
11
Views
554
Replies
2
Views
1K
Replies
2
Views
350
Replies
1
Views
407
Replies
2
Views
435

1,191,701
Messages
5,988,176
Members
440,135
Latest member
DCDavid

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back