Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Combo Box (Excel 2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box (Excel 2003)

    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:

    <table border=1><td></td><td align=center>K</td><td align=center>1</td><td>16-19</td><td align=center>2</td><td>20-24</td><td align=center>3</td><td>25-29</td><td align=center>4</td><td>30-34</td><td align=center>5</td><td>35-39</td><td align=center>6</td><td>40-44</td><td align=center>7</td><td>45-49</td><td align=center>8</td><td>50-54</td><td align=center>9</td><td>55-59</td><td align=center>10</td><td align=right>60+</td></table>

    - 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.

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Combo Box (Excel 2003)

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

    Kerry

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Combo Box (Excel 2003)

    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 <code>ListAges</code> and then in the Source box when setting up data validation you would enter <code>=ListAges</code> instead of <code>=K1:K10</code>
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Combo Box (Excel 2003)

    Thanks this is also very useful.

    Regards Kerry

  6. #6
    Lounger
    Join Date
    Jun 2007
    Location
    Salem, Oregon
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box (Excel 2003)

    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.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box (Excel 2003)

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

  8. #8
    Lounger
    Join Date
    Jun 2007
    Location
    Salem, Oregon
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box (Excel 2003)

    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.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box (Excel 2003)

    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:
    <code>
    =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))</code>

  10. #10
    Lounger
    Join Date
    Jun 2007
    Location
    Salem, Oregon
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box (Excel 2003)

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

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box (Excel 2003)

    The INDIRECT function evaluates a string argument that represents a range. To take a simple example:
    <code>
    =SUM(INDIRECT("A1:A4"))
    </code>
    is equivalent to
    <code>
    =SUM(A1:A4)
    </code>
    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>
    =CODE(INDIRECT(B1))
    </code>
    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
    <code>
    =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))
    </code>
    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.

  12. #12
    Lounger
    Join Date
    Jun 2007
    Location
    Salem, Oregon
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box (Excel 2003)

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

  13. #13
    Lounger
    Join Date
    Jun 2007
    Location
    Salem, Oregon
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box (Excel 2003)

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

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box (Excel 2003)

    The reference to a cell on another sheet looks like this:
    <code>
    Sheet1!A1
    </code>
    If the sheet name contains spaces or if it doesn't begin with a letter, the sheet name is enclosed in single quotes:
    <code>
    '4Q06'!A1
    </code>
    Since we are assembling the reference to the sheet, we start by a string containing a single quote:
    <code>
    "'"
    </code>
    and concatenate it with the value of cell D2:
    <code>
    "'" & $D$2
    </code>
    etc.

  15. #15
    Lounger
    Join Date
    Jun 2007
    Location
    Salem, Oregon
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box (Excel 2003)

    Thanks! This helps a lot!

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •