Combo Box (Excel 2003)

Topic

New Reply

Can anyone tell me how to make a combo box in a spreadsheet. I want the user to be able to select from a list of entries – see example below. I dont want to use the autocomplete option.

GENDER
MALE
FEMALE

AGE GROUP
16-19
20-24
25-29
30-34
35-39
40-44
45-49
50-54
55-59
60+

ACCEPTED
REJECTED

Reply | Quote

Replies

You can use Data | Validation. For example for Age Group:
– Enter the values for the list in a range on your worksheet. For example in K1:K10:

	K
1	16-19
2	20-24
3	25-29
4	30-34
5	35-39
6	40-44
7	45-49
8	50-54
9	55-59
10	60+

– Select the cell or cells where you want to display a dropdown list.
– Select Data | Validation…
– Select List in the Allow dropdown list.
– Click in the Source box and point to the list or enter the address K1:K10.
– Activate the Input Message tab and enter an instructive message (if desired)
– Activate the Error Alert tab and enter a message to be displayed if the user enters an incorrect value.
– If you don’t want to allow other values than those from the list, make sure that the Stop style has been selected.
– Click OK.

Similar for the other lists.

Reply | Quote

Thanks again Hans – this is exactly what I was looking for.

Kerry

Reply | Quote

One additional point – if the data you want to use for the lists is on a different worksheet than the cells where you want the dropdowns, you must use named ranges. So, following Hans’ example, you could name the K1:K10 range ListAges and then in the Source box when setting up data validation you would enter =ListAges instead of =K1:K10
FWIW.

Reply | Quote

Thanks this is also very useful.

Regards Kerry

Reply | Quote

I was wondering if it is possible to use validation feature for looking up data on 12 different spreadsheets or 12 tabs? There are 4 spreadsheets for each yr (4 Qtrs) and there are 3 yrs. There is a summary spreadsheet that lists totals from the each qtr spreadsheet. I want to be able choose yr, qtr and portfolio on a summary spreadsheet. I was able to make portofolio list work with just 4 spreadsheets, but not with twelve. I tried to select the whole spreadsheet and gave it a name, but that did work. If someone knows how can I make it work, please help. See attached spreadsheet.

Reply | Quote

Can you explain how the summary worksheet works? I don’t understand how it relates to the other worksheets.

Reply | Quote

First, I had only one tab with data 1Q07 and a Summary tab. You helped me create a formula that was looking at dates on 1Q07 tab and pull all the data that met this criteria ( I entered formulas in cells G4:G22 in the attached example). I entered reference dates below on Summary tab. Every time I change portfolio cell C4. Cells F50:G57 vlookup correct range of dates. Cells G5:G22 refrence the these cells below and change accordingly. I can make it work only for one year (I’ll have to have columns D trough F refference diffrent quarters, that are in diffrent tabs). I want be able to change yrs C3 and quarters D2:G2. For example, when yr 2006 4th qtr (this is one tab, there will be also 3Q06, 2Q06, 1Q06) is choosen I want Summary tab to pull data from only 4Q06 tab. I hope this explains it a little better.

Reply | Quote

Do the formulas in D5:G5 in the attached version do what you want? They use the INDIRECT function to assemble the sheet name from the year and quarter, for example in D5:

=SUMPRODUCT((INDIRECT("'"&D$2&RIGHT($C$3,2)&"'!$E$9:$E$254")>=$F$50)*(INDIRECT("'"&D$2&RIGHT($C$3,2)&"'!$E$9:$E$254")<=$G$50))

Reply | Quote

Sorry, I just got back to working on this project. This formula works!!! I don’t really understand how it reads exactly which spreadsheet to reference though. How does INDIRECT formula in your attached spreadsheet work? =SUMPRODUCT((INDIRECT(“‘”&D$2&RIGHT($C$3,2)&”‘!$d$6:$d$254”)>=$F$50)*(INDIRECT(“‘”&D$2&RIGHT($C$3,2)&”‘!$d$6:$d$254”)<=$G$50))

Reply | Quote

The INDIRECT function evaluates a string argument that represents a range. To take a simple example:

=SUM(INDIRECT("A1:A4"))

is equivalent to

=SUM(A1:A4)

This example is not very interesting, of course, since the string is constant. The fun begins when you assemble the string by reading other cells. For example, if the cell B1 contains the text A1:A4, the formula

=CODE(INDIRECT(B1))

is equivalent to the above, but if you change the text in B1, the result of the formula will change accordingly. And you can assemble the string by concatenating various parts.

In the formula

=SUMPRODUCT((INDIRECT("'"&D$2&RIGHT($C$3,2)&"'!$E$9:$E$254")>=$F$50)*(INDIRECT("'"&D$2&RIGHT($C$3,2)&"'!$E$9:$E$254")<=$G$50))

D2 contains the quarter (4Q) and C3 contains the year (2006). RIGHT(C3,2) returms the last to digits of the year (06). These are concatenated to 4Q06. This is used as the name of the sheet to look at.

Reply | Quote

That makes sence! Why did you put quation marks right after Inderect and after & sign and at the end of the Inderect formula?

Reply | Quote

I see one par of quotation is for the String? What about opening quotation right after the INDIRECT?

Reply | Quote

The reference to a cell on another sheet looks like this:

Sheet1!A1

If the sheet name contains spaces or if it doesn’t begin with a letter, the sheet name is enclosed in single quotes:

'4Q06'!A1

Since we are assembling the reference to the sheet, we start by a string containing a single quote:

"'"

and concatenate it with the value of cell D2:

"'" & $D$2

etc.

Reply | Quote

Thanks! This helps a lot!

Reply | Quote

One small argument. I find the example:


=SUM(INDIRECT("A1:A4"))


to be very interesting. Since “A1:A4” is a string, then no matter what you do with that range (like deleting rows or columns), or what you do with the formula (like copy or fill it to other cells), the function remains exactly the same, it still sums A1:A4. That can be extremely interesting and useful.

Reply | Quote

OK, I agree that it can be useful, but it wasn’t the most interesting example in the course of explaining what INDIRECT does…

Reply | Quote