Defined Name Table Refinement

You are going down a blind alley.

I use multiple text boxes to search for a product.

you need some visual basic, but it is quite simple once you have a sample.

This is a Sheet Based version:

I am happy to help you adapt either version to meet your requirements.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

What you are after is really easy. [ Famous Last Words ]

I would create six drop down boxes

Selecting the six boxes would populate a list box

Clicking on the listbox will select your line.

You say there are tw manufacturers but list only one.

If you post a sample I will create something for you.

Sorry this is harder than I expected.

This is my first pass using dummy data

It has taken me ages to set up the Data Validation.

Give them a go while I work out the next bit for you.

Ok

open the attached file, it is preloaded with search data

select the "Search" Tab, this will run the macro and display the list box.

click on an entry, this will copy the entry into row 1.


Play with this, and let me know where you would like to go from here.

Ok so now you are happyish

I will tell you what I have done. It would have been pointless before.

Firstly did you see the uniquely sorted lists on the right of the data? Columns M to P inclusive?

Those are the values that I used to create the Data Validation Lists in rows 3 and 4, side stepping the need for ComboBoxes.

Lets focus on one of those lists.

M1 to M 5 Contains the following values:-

Manu
DH
KF
KM
PO

This is a Unique Sorted List based on the data in your column B.

The formula used is the array formula:

Formula:

Please Login or Register  to view this content.

Entered using Ctrl Shift Enter.

Manu

Refers to a Dynamic Named Range which will adjust to cope with the amount of data you have ie as you add more rows.

Select Formula, Name Manager and you will see that I have created 8 of these.

The formula for Manu is:-

Formula:

Please Login or Register  to view this content.

This link is to a video that explains the above two formulas in great detail and a lot better than I could:-

https://www.youtube.com/watch?v=IZLAzIYfMDU


Manu2

Formula:

Please Login or Register  to view this content.

This returns M2:M5 which is the list I want to use for the data validation in B3

NB: This is a revised formula


This formula works too:

It is trial and error I am afraid

Formula:

Please Login or Register  to view this content.

Similar formulas for all 4 Data Validations

Then I Selected Data Validation, selected from a list

And used the formula

Formula:

Please Login or Register  to view this content.

to tell excell what list to use.




Now for the Macros

The first and simplest macro is in the Database Sheet, it is therefore sheet specific.

Right Click on Database at the bottom of excel and select view code.

this code will be displayed:

Please Login or Register  to view this content.

The Next Macro is also sheet specific but because I am using the tab as a macro button, it is more difficult to view.

Select the Developer tab and then visual basic.

Select view and then View and Project Explorer

Double Click on Sheet2(Search)

you should then see this code:-
I think it is self explanatory

Please Login or Register  to view this content.

The final two Macros are in the userform.

You access then in the same way as the last macro.

Select the Developer tab and then visual basic.

Select view and then View and Project Explorer

Select the Userform

Double click on the userform

Please Login or Register  to view this content.

The Last Macro is run when you select an entry on the listbox.

Please Login or Register  to view this content.