Multiple ComboBoxes based on same dynamic Data- Help.

[JaveCidem]

Good day,
I'm developing an order form for my workplace. I initially used DropDowns (Data Validation Method); however, upon trailing the form, our staff found it hard to scroll through an inventory list of 400 items.
Last night I searched and found an answer to searchable lists: ActiveXComboBox! So instead of having 40 rows of dropdown lists, I have 40 ComboBoxes. The order form in on the mainsheet, and the data source is on the 2nd sheet (which I have protected and hide from the users).

Here is my problem: I want the comboboxes to be linked to the same table and use the same helper columns to retrieve the right data without creating 3 helper columns x 40 comboboxes (which are all based on the same Master Table.

These are my helper column formulas:

• (If Found Column is in "S"): =--isnumber(search('Sheet2'!$B$11, TableMaster[Products]))
• (Frequency Column is in "T") =if(S4:S400=1, COUNTIF($S$4:$S4,1),"")
• (Search helper Column is in "U") =IFERROR(INDEX(TableMaster[Products],MATCH(ROWS($T$4:$T4),$T$4:$T$400,0)),"")
• I've named my Range for ComboBox1 as SearchBox1. This is the formula I've used: =Sheet2!$U$4:INDEX(SHEET2!$U$4:$U$400,COUNTIF(SHEET2!$U$4:$U$400, "?*"))

Please Note that this works for ComboBox1

Ideally, I would like all 40 ComboBoxes to be able to use the three aforementioned Helper Columns and MasterTable - without re-creating 120 Columns for the 40 ComboBoxes.

OR is there a way to "Stamp" or select a product item from one comboBox and have it "punch" or be pasted into a cell. Then move to the next row (same column)?

The goal is to make this order form easy to use, which is why I needed the search field in the product item cells. This will allow the staff to start typing what they think the product is called and it will return the value of all items with the searched work. Example, "Tape" (options Transport, Mircopore Tape, etc.)

I have take 4 days of excel training. I am a Military Medic who has been placed into the role of SupplyTech. I have little experience with Excel; however, I have been consumed with this project and have been working 7 days a week to get our Medical Stores Position Turned around and with complete transparency. It's scary how much stuff "disappears" or wasted products (mass ordering - expired prior to use).

Thank you Excel Friend in advanced. I am here to learn and grow.

Dave
Cidem is Medic Backwards FYI.

 

[My Aswer Is This]

I think it would be nice if you were to tell us when you select a value from the combobox what do you want to happen.
I see no reason why you would need 40 Comboboxes.
So for example say:
When i choose a value from combobox1 I want this value entered into Range("A1")
Next time I select a value in Combobox1 I want this value entered into Range("A2")
etc. etc.
Give sheet names and the name of the Combobox.

 

[JaveCidem]

Hello My Aswer IS This,
Yes, that would be amazing if only 1 combobox was employed to fill the 40 rows with products that the Care Delivery Units require.

I have the main sheet (Called MedTech MedStore Order Form) and the hidden worksheet (I've called it MasterSheet).

Is that possible to only have one combobox? I would love any help with this.
Also, what if the user makes a mistake and wants to re-enter a value in one of the cells, how would you control which cell the ComboBox enters into next?

Jave

I think it would be nice if you were to tell us when you select a value from the combobox what do you want to happen.
I see no reason why you would need 40 Comboboxes.
So for example say:
When i choose a value from combobox1 I want this value entered into Range("A1")
Next time I select a value in Combobox1 I want this value entered into Range("A2")
etc. etc.
Give sheet names and the name of the Combobox.

 

[My Aswer Is This]

I need more details if I'm going to be able to help you.
And are you sure you want to use a Combobox?
A Combobox gives the user the ability to use a value he manually entered into the Combobox.
As a listbox restricts a user from only using values in the listbox

And to write the entire script for you I need to know where is the list of values we want to load into the listbox?

And when a value is selected from the listbox and then a button is pushed where do you want these values entered.

Again I need listbox name where are these values going etc.

If the user made a mistake he could manually change the cell that was changed of delete it and start again.

Where on the data source sheet is the list to be loaded into the Listbox.


Be sure and understand all i know is what you tell me. So give me as many details as possible.

This can take a long time otherwise.

So my plan would be we have a list box with loaded values.
A user selects the value they want in the listbox
Then the user presses a command button and that value is entered into some column

And is there any thing else that needs to happen?

And I don't think you need all those helper columns and formulas.
Just tell me exactly what you want to happen.

 

[JaveCidem]

Hello My Anser is This,
I just responded to your post. I tried to drag a photo into the window and now I can't find everything I wrote to you about. Frustrating. Grrr. lol

Okay, I have considered the dropdown list and am presently using the Data Validation DropDown List option, however, I can't figure a way to have the list change depending on what the user inputs, to refine the search and only display results that contain the same text. Ex, the user could enter Tape, and all items that I have which have tape in item description would appear (transpore Tape, surgical tape, Micropore, Athletic Coach Tape, etc).

My first post explained the formulas I was using to accomplish a "searchable" dropdown list. ComboBoxes/the formulas work. I was hoping there would be a better way to accomplish a searchable dropdown than typing out 44 dropdown comboboxes with 3 helper cells each (120 helper columns).

I like the idea of a search field which would input the data into the specific column. That is excellent.
Please help, but I would appreciate your patients with my silly questions. You are skilled and very knowledgable. I don't have the knowledge which you have. BUT I want to learn. I want to understand.

Here is the raw data:
I have two sheets. "MedTech Order Sheet" (main sheet with Order Form on it) and "MOMS" (stands for MedTech Order for Medical Stores, this sheet has the master table. This table is sourcing/providing the point of reference for the search fields/Vlookups/INDEX&MATCHES, etc. Additionally, I've linked this sheet table to another table on my real MasterTable, which is on another file. The goal is that as I update my MasterInventory Sheet, the Orderform MasterSheet will also automatically update and provide the user with the most uptodate items we presently have in stock).

My order form table stars on Row 9 and has 6 columns (A to F).
The headers/Columns are labeled as:
Column 1 - No.
Column 2 - Item (this is where I want the items to be added, or the dropdown to be (searchable))
Column 3 - Qty (the user will enter in the quantity of the Item they need).
Column 4 - Unit (I'm using dependent lists based on the Item column. Each Item has a different unit of measurement. Example, Each Roll of tape, Bag of tape, Box of Tape, Case. Or a box of bandages, Minor surgery tray, roll of Mepore bandage. Initially, I had one drop down list with EA/BOX/CASE. However, I quickly discovered that some people need things to be very clear. If they order a roll of Leukotape, they will actually look for the word "roll". My dependent lists change per item).
Column 5 - "Total Items" - Here I clearly show the MedTech how many of the item they are requesting. This is Index'd and Match'd to the masterlist on MOMS. Depending on the Unit, the Qty will be multiplied by the quantity in a box or case. I have columns in my MOMS MasterTable explaining the quantities for each item. Things are not always simple, products each are offered/sold in different quantities. This, too, works. I like them user to be able to see exactly how many of the item they are order. Additionally, using the same formula, I can change the Total Items to the actually cost of that item. I can foresee this being useful when when it's time to invoice other units.
Column 6 - "Neeb Notes". This section allows me to put a note next to each item and tell the user any pertinent information they should know about it. Ex. We are trailing new surgical blades, I'd like to specify that their blade selection is on a clinical trail. Or an item is backordered from the suppliers, etc.

I have approximately 44 rows (Row 9 -53).

Row 1 is the Title
Row 2 Blank
Row 3-6 are Date/Location/Person Requesting/Request Date (all in Column A). In column C, I have Filled by/Date.

I have a button at the bottom of the page, rows 66-71 (I know, it's a large button), with a macro assigned to it which will save the file with Date/Time (military formate/24) and the file name. It will then attach the file, email it to my positional mailbox and close Excel.

Is this too much data?

Please let me know what you would want to know about the MOMS sheet.
I can tell you that the ITEM column (it's in a table as well and "Description" is the header of that column). The raw data starts on C4 and the column is presently goes to C382. I am constantly adding Data and it will grow.

To recap, I need the dropdown to be searchable or a way to easily search for items. I have been doing medical ordering for the past 2.5 months and am more familiar with the products and their proper "ordering" name. However, the users this is intended for will know a word or two. I just want it to be easy for them to add items.

At present, my form works. My workplace has made a large step going green (reducing our paper waste). I need your help/expertise to make this better. If you don't mind helping.


Thank you for your time/patients reading this.

If I can offer any other pieces of information, please let me know.

 

[JaveCidem]

My list Range name is called "MasterListItems". It's =TableMOMS[Description]
When the button is pushed, I want the data to be placed into Column 2 ("Items").

The only other feedback, I've received is that it would be nice if the mousewheel worked when scrolling in the list. However, I think I found something about this that I can work through on my own for now.

I am here to learn. I am new to MrExcel and have started to purchase resources to help me learn. If you can think of any resource that really helped you, please share let me know.

Thank you again for your time and patients.

Jave

 

[My Aswer Is This]

This is beyond my knowledgebase.
I was just providing a way to do things without needing 54 Comboboxes.

I'm sure someone else here at Mr. Excel will see this posting and try to help you.

I will continue to monitor this thread to see what I can learn.

 

[JaveCidem]

Hello My Aswer is This,
Thank you for your honesty.
I will see if I can create a macro. Explaining to you what I wanted to accomplish has helped me to phrase/think about my current problem. I can probably use one combobox, as I have done w/the searchable options. I would then take the value, have it copied into the next blank row in my 2nd column. Then clear the field of the ComboBox.

Thanks,
Jave

 

[My Aswer Is This]

If you have some code you have written. Show it to us and maybe we can give you some help.
I deal better with small requests and one step at a time.

You said:

The only other feedback, I've received is that it would be nice if the mousewheel worked when scrolling in the list.​

When using a Combobox you can scroll the list with the Up and down arrow keys on your keypad

 

[JaveCidem]

If you have some code you have written. Show it to us and maybe we can give you some help.
I deal better with small requests and one step at a time.

You said:

The only other feedback, I've received is that it would be nice if the mousewheel worked when scrolling in the list.​

When using a Combobox you can scroll the list with the Up and down arrow keys on your keypad

Good evening,
Yes, I knew that. I received feedback and the users would find it easier to use if they could use the mouse instead of scrolling through a long list of items.
Although, if the ComboBox works for the search feature, the mousewheel functioning might not be necessay. lol

Jave