• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Issue with Listbox

C

cacos

Member
Hi everyone!


I'm having an issue with Listboxs. I have two, and the 2nd one is loaded depending on what the user selected on the 1rst listbox (it has a named range that changes dynamically).


The thing is, there are blank spaces on list 1 and list 2. And I cannot remove them because some lists are shorter than the others. So whenever a user selects a blank item, all the charts that depend on that entry go blank.


Is there any way to correct this?


Thanks
 
Cacos...I'm definitely not one of the excel experts around here, but I had the same issue with my lists with multiple drop downs.


I had to make a helper column to achieve this. Then I used this array {enter with ctrl-shift-enter} formula to erase and reorder the list and erase the blanks:


=IF(COUNTIF(A:A,$I$15) < ROWS($M$2:M2), "", INDEX(C:C, SMALL( IF(allStates =$I$15, ROW( allStates)), ROW(A1))))
 
Thanks maku! I'll try it out now. Anyway, I know there's a way that makes the user unable to click on the blank items. I'm trying to figure it out (without much luck so far)
 
Hi ,


If you can go through this site , you will get what you are looking for. Since the original is in Italian , you can use Google Translate to get it translated to yours , if you want.


https://sites.google.com/site/e90e50fx/?offset=40


Listboxes should not be loaded from a raw list ; preferably duplicates and blanks should be eliminated , and the list should be ordered before the listbox is populated with the items. You will find the necessary formulae in the link given.


Narayan
 
Hi Narayan, ranges don't have fixed lenght, it varies. Ill check the link to see if it works.


Thanks!
 
I've tried loading the Listbox with a dynamic range using OFFSET but somehow it's not working OK


The offset range looks like this: =OFFSET(Data!$J$6,0,0,COUNTA(J6:J13)-COUNTIFS(J6:J13,""),1)
 
Also, I've noticed that it works OK when using a Form Control ListBox, but it fails on the ActiveX control
 
Hi Cacos,


Can you see this, it has a dependent drop down-list that shows diff. countries for each region. (Uses form controls)


http://dl.dropbox.com/u/60644346/Copy%20of%20Dinesh121_Resolved2.xlsx


Regards,
 
Thanks Faseeh, it works perfectly. Is there no way of doing the same with a Listbox ActiveX?


That fill ranges are dynamic?
 
You must log in or register to reply here.
Back
Top