Results 1 to 5 of 5
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    dynamically set items in Combobox (Excel 2003)

    I have a combo box (from Forms toolbar) on a worksheet which I use to let the user select which of 10 (max) charts they want to view. With help from fellow Loungers, this work very well. Now I want to have the combo box only show the the list of available charts. Each chart represents a service which has a name (or not if it hasn't been defined). I store a copy of these names in a list like this (the data source for the combo box):

    A1=name1
    A2=name2
    A3=name3
    A4=name4
    A5=
    A6=
    A7=
    A8=
    A9=
    A10=

    (So this shows 4 names are defined, 6 are not). I played with the OFFSET formula but can't get the combo box to only list the non-blank names. Actually, that's not quite correct, it lists the entire 10 cells (including blanks) but it still lets the user select one of the blank spots. So if 4 names are defined, there are 6 empty spots in the combo box drop down that the user can still select.

    How can I adjust it such that the drop down list only shows the defined names (1-4 above example), not all 10 cells (including empty ones)?

    Thnx, Deb

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

    Re: dynamically set items in Combobox (Excel 2003)

    You can define a dynamic range:
    - Select Insert | Name | Define...
    - Type a name, e.g. MyList.
    - In the Refers To box, enter the following formula (substituting the appropriate sheet name):

    =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A))

    - Click OK

    Now assign this dynamic range to the combo box:
    - Right click the combo box.
    - Select Format Control from the popup menu.
    - Activate the Control tab.
    - Click in the Input Range box.
    - Enter the formula =MyList
    - Click OK.

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dynamically set items in Combobox (Excel 2003)

    This is very similar to what I had tried myself but the COUNTA() returns the entire 10 item list even though not all are filled. The 'empty' items have "" in them (from a formula that determines what names to use), not 0. I changed the empty fields to 0 but COUNTA() still returns 10. Instead of A:A I have the exact address of the list which is D917926. I moved the list to an unused column and did $N:$N as a test but the result was the same (too many names show).

    1. ServiceA
    2. ServiceB
    3. ServiceC
    4. ""
    5. ""
    etc. where "" represents the 'emtpy' cell.

    The combobox scrolls to to show all 10 items whereas I only want to show the non-empty items (1-3 in my example here).

    Thnx, Deb

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

    Re: dynamically set items in Combobox (Excel 2003)

    You see how important it is to provide all relevant information. I had assumed that the 'empty' cells were blank. Try this as Refers To for the dynamic range:
    <code>
    =OFFSET(Sheet1!$A$1,0,0,SUM(IF(Sheet1!$A$1:$A$10=" ",0,1)))</code>

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dynamically set items in Combobox (Excel 2003)

    PERFECT!! Works great, thank you, thank you <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    Deb

Posting Permissions

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