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

Dynamic combo list linked to a named table, displaying random vendors at bottom of list?

Ainsdale

New Member
Hi

I am new to this forum. Please excuse any mistakes in etiquette etc.

I am setting up a form to select a list of vendors and have used an active x combo box as a list. I have linked that list to a named range "Vendors".

That named range is linked to a table. I have used a table so the list is dynamic, to enable future extension of that list by adding new vendors.

I have used the following formula in the "Vendors" named range =Lists!$B$2:INDEX(Lists!$B:$B,COUNTA(Lists!$B:$B,1))

Note: $B$2 is the 1st cell in the table range, but that cell is also BLANK. Not sure if this is relevant or not, as I know COUNTA generally ignores blanks. However, I need a blank in the drop down so initially the form has blank cells, prior to a user populating.

The issue I have is that the drop down list keeps adding a couple of random Vendor's names at the very bottom of the list (those vendors already exist higher up in the list). It is rather bizarre and I can't fathom why? The random names vary also, if I activate the list and scroll up and down those random names change to 2 other names? Perhaps it is my formula? But all I need to do is have a dynamic drop down, which increases as new vendors are added to the list/table.

Any help would be greatly appreciated.
Many thanks
 
Hi
Thanks for your response.
Typically when I changed the file to the attached 'Test' file, the issue stopped happening!.... However, when i added "test2" onto the bottom of the vendor table (Cell B359) in the 'Lists' worksheet it did not initially show up on the 'Vendor' drop-down on the 'FORM' worksheet until I closed and re-opened the file, is that a requirement for these types of active combo lists?

Ideally the list needs to be dynamic so people can add new vendors easily to the drop down, but the table list seems to be falling down somehow? If you have any other suggestions for a dynamic list I would be grateful.

I also still don't know why the random vendor repeat at the bottom of the list on my master file occurred, but if you have any ideas why such a thing may occur then I would be grateful.

Thanks again
Mike
 

Attachments

  • Purchase requisition FORM - TEST.xlsm
    40 KB · Views: 3
Ainsdale
You've already 357 Vendors ... with combo ... and You will add there more of those ...
Could You check ... something ... different?
... You could see more items in same time
... You could filter, sort those items
There are short instructions.

Ask more, if You're interesting in.
> My Excel won't work with ActiveX-components <
 

Attachments

  • mike ainsdale.xlsb
    46.1 KB · Views: 2
Back
Top