Defined Name Table Refinement

[Mojo Jojo 89]

I have limited Excel experience. I recently tried a database type of functionality by using a chart, defining a named group and using validation for a drop down list of products. Then I use Index in a formula to find other chart values of the selected item, such as product specifications and pricing. This worked out great.

But the drop down list is 104 items long. I would like to add some user input cells to constrain the drop down interactively by multiple criteria. I'm not sure how best to accomplish this.

My Chart has Manufacturer, part number, Size, orientation that are key to choosing the right product.

I could add fields for the user to choose one of the two manufactures. They orientation is also a binary choice. Then unit size, which is a BTU range.

What I want to accomplish is to enter those values and have the drop down list trimmed down to just the items that meet it.

What is the best function/approach/method of doing that? I've read some about filtering, advanced filtering, but I'm not sure which one is actually capable of the end result I'm trying to achieve.

Can someone please point me in the right direction?

[mehmetcik]

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.

[Mojo Jojo 89]

Much Appreciated! I got the feeling I could pour a lot of time in the wrong direction.

I don't know anything about VB, really.

[Mojo Jojo 89]

This is name defined chart:

Untitled.jpg

These are the values the user could specify:

Manufacturer (which is binary choice)
Orientation (which is binary)
BTUs (which would be 2 fields defining a range. Such as 70,000 to 90,000)
Blower Cap (also 2 fields defining a range. Such as 24,000 to 36,000)

How would that be written in VB, generally?

[mehmetcik]

This is a Sheet Based version:

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

[mehmetcik]

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.

[mehmetcik]

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

[Mojo Jojo 89]

Working on that AFTER page right now. What does desensitize data mean?

[Mojo Jojo 89]

The Before is really the Settings and Proposal Sheets. In the proposal Sheets, F16, K16, P16 and U16 are the set of dropdowns I want filter. Those named groups are from the Furnaces sheet.

The After page has more details on the output i'm trying to achieve.

Thanks

[mehmetcik]

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.

[Mojo Jojo 89]

Looks good so far!

[mehmetcik]

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.

[Mojo Jojo 89]

Wonderful! That's the stuff. So I now I need to figure out the details.
So I noticed the new stuff in the defined names. Each column were specifically using as criteria has 2 named groups. The first is:
=INDIRECT("K5:K" & MATCH(9.99999999999999E+307,Database!$K:$K))
I'm not familiar with what Indirect does. I looked it up but the general definition doesn't help me. I don't understand the match bit with the 9.999, but I do see the Database bit.
The 2nd named group is:
=INDIRECT("P2:P" & COUNTIF(Database!$P:$P,">0")+1)
I think I can figure that one out, but if please let me know what the gist of it is, what its doing.

Ok, so beyond this and the vb code, is there any other setup that is not readily observable?

For the VB Code, that's something I have never tried. I have done some code. In high school. In the 80s. Was pretty good with Basic and Pascal.

Were is the code entered? How can I observe what is currently embedded within the spreadsheet?

[mehmetcik]

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:

=IFERROR(INDEX(Manu,MATCH(0,COUNTIF(Manu,"<"  &Manu)-SUM(COUNTIF(Manu,M$1:M1)),0)),"")

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:-

=INDIRECT("B5:B" & MATCH("zzz",Database!B$1:$B$20000,1))

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

=INDIRECT("M2:M" & SUMPRODUCT(--(LEN(Database!M:M)>0),--(Database!M:M<>"")))

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

=INDIRECT("M2:M" & SUM(--(LEN(Database!M1:M20000)>0)))

Similar formulas for all 4 Data Validations

Then I Selected Data Validation, selected from a list

And used the formula

=Manu2

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:

Private Sub Worksheet_Change(ByVal Target As Range)

'This macro runs whenever a change is made on the sheet
'It is simply sorting your BTUs and your Blower Caps

If Range("F3").Value > Range("F4").Value Then
Temp = Range("F3").Value
Range("F3").Value = Range("F4").Value
Range("F4").Value = Temp
End If

If Range("K3").Value > Range("K4").Value Then
Temp = Range("K3").Value
Range("K3").Value = Range("K4").Value
Range("K4").Value = Temp
End If

End Sub

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

Private Sub Worksheet_Activate()

Sheets("Database").Select
UserForm1.Show
End Sub

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

'This is the Crucial part
'It loads your listbox with the data matching your criteria

Private Sub UserForm_Activate()

'How many rows of data do you have?
LR = Cells(Rows.Count, 1).End(xlUp).Row

'Looking in Column B
    With Columns("B:B")

'Find an entry Matching your Manufacturer
        Set rngFind = .Find(Range("B3").Value, .Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
        If Not rngFind Is Nothing Then
            strFirstAddress = rngFind.Address
            Set rngPicked = rngFind
            Do

'rngPicked is a list of cell references matching your manufacturer
                Set rngPicked = Union(rngPicked, rngFind)

'find next matching cell
                Set rngFind = .FindNext(rngFind)

'keep looping until you run out of cells of you get back to the first cell
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
        End If
    End With
  

If strFirstAddress = "" Then Exit Sub

If strFirstAddress = "" Then Exit Sub

'This selects all matching cells.

rngPicked.Select

'This Loops Through all the cells and checks if all the criteria match
For Each c In Selection

If c.Row < 5 Then GoTo Skip

If Cells(c.Row, 2) <> Range("B3").Value Or Cells(c.Row, 5) <> Range("E3").Value Then GoTo Skip

If Cells(c.Row, 6) < Range("F3").Value Or Cells(c.Row, 6) > Range("F4").Value Then GoTo Skip

If Cells(c.Row, 11) < Range("K3").Value Or Cells(c.Row, 11) > Range("K4").Value Then GoTo Skip

'If it matches create a new entry and save the row number in column 1
ListBox1.AddItem c.Row

'Store the data from columns 2 to 6 in your data ( B to F)
For Count = 2 To 6
t = ListBox1.ListCount
ListBox1.List(ListBox1.ListCount - 1, Count - 1) = Cells(c.Row, Count).Value
Next

'Store the data from columns 8 to 11 in your data
For Count = 8 To 11
t = ListBox1.ListCount
ListBox1.List(ListBox1.ListCount - 1, Count - 2) = Cells(c.Row, Count).Value
Next

Skip:
Next
End Sub

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

Private Sub ListBox1_Click()

'This returns the contents of column 1 of the list box, which is the row number on the database sheet
MyRow = ListBox1.Value

This writes Column 1 to the first line of the database
Range("A1").Value = MyRow

'Writes the next 5 columns
For Count = 2 To 6
Cells(1, Count).Value = Cells(MyRow, Count).Value
Next

Writes the last 4 columns
For Count = 8 To 11
Cells(1, Count).Value = Cells(MyRow, Count).Value
Next

Unload Me

End Sub

[Mojo Jojo 89]

Wow! Thank you for tremendous detail in walking me through it. My work schedule prevents me from really digging in to it again until this weekend, but this gives me a wealth of knowledge to get it done. I have some other tables that need similar features, but it surely is just a matter of reference adjustments. Many, Many Thanks!