COMBO BOX with INDEX/MATCH. Arrange COMBO BOX A to Z, with no repetition, only relevant?

[srands]

FILE hyperlink below is spreadsheet of COMBO BOXs
http://www.1sar.karoo.net/exoftable0.xls file size 111kb's

It is a simple DATABASE of CARS, with a RESULTS PAGE, much like an excel version of AUTOTRADER, a basic example without photos & descriptions.

The Combo Boxs are:
COLUMN A: CAR MAKE
COLUMN B: CAR MODEL
COLUMN C: CAR TYPE
COLUMN D: CAR COLOUR

The criteria of Combo Box's in 'ComboBox CHOICE & RESULTS', sorts results from 'WORKSHEET'.

My spreadsheet looks ok, however the combo box's I've made lack the functionality I need, and to edit them further I'm unfamiliar with, hence they need editing to:

i) Arrange COMBO BOXs to display in Alphabetical order, A to Z of available fields

ii) or even better, arrange COMBO BOX A to Z, with no repetition

iii) Combo Box's following from Previous, left to right, only to display relevant fields possible based on previous selections , not all fields in that column listed in COMBO BOX, PROPERTIES, ListFillRange, some examples below:

Example 1:
COLUMN A: If PEUGEOT is selected, then in
COLUMN B: The only choices would be 206 or 207 or 306 or 307
etc

or

Example 2:
COLUMN A: If PEUGEOT is selected, then in
COLUMN B: If 206 is selected, then in
COLUMN C: only HDi would be available, then in
COLUMN D: The only choices would be SILVER or BLUE

Perhaps there are better, also easier to use spreadsheets, obviously the WORKSHEET spreadsheet is very good, and the autofilter is fast & effective at the same tasks described here, however I want a COMBO BOX spreadsheet without seeing the total full list of entries in the WORKSHEET.

However I haven't seen/found or worked with such an example so far. My intention is that copies for other purposes can easily changed, by changing the titles, and then results worksheet sort is left unchanged hence will only display fields to choose from.

 

[MickG]

Try this:-

https://www.box.com/s/t9t1bkwol8carcsnw383
Not sure why this file has got so big, theres only a few pictures.
If its Ok you can take the code out an use in your own File !1

 

[srands]

Hi Mick, the spreadsheet looks great. However an error occurs when I add further IMAGELISTs, even with correct naming of ComboBox 1 & 2, without spaces.

Also when the above is sorted out, what about when there is more then 1 of same MAKE & MODEL?

Will the VB code/Formula realise it is a different ROW/CAR?

 

[srands]

Hi Mick, I have successfully created a non-macro spreadsheet that displays 5 photos in RESULTS from WORKSHEET for the 1st row of PHOTOMstList (1st spreadsheet), using COMBOBOX in A2 of PHOTOSELECTOR (2nd spreadsheet).
http://www.1sar.karoo.net/PHOTOs~NonMacro2.xls 944 kb's

However unsure how to integrate this into my main spreadsheet, per each result in RESULTS COLUMNS F to J:

http://www.1sar.karoo.net/exoftable3~PhotoMacro.xls 907 kb's

I guess this will be some simple FORMULA code or VB code, however presently I don't know!

Currently the PHOTO spreadsheet http://www.1sar.karoo.net/PHOTOs~NonMacro2.xls 944 kb's
is simply a list with 5 photos per car, and the following code:

INSERT, DEFINE, NAME: PictureList REFERS TO: =OFFSET(PHOTOMstList$A$1,1,0,COUNTA(PHOTOMstList$A:$A)-1,1)

INSERT, DEFINE, NAME: Picture1 </SPAN>REFERS TO: =OFFSET(PHOTOMstList!</SPAN>$B$2</SPAN>,MATCH(PHOTOSELECTOR!$A$2,PictureList,0)-1,0,1,1)</SPAN>

Sheet2, B2, insert object, create from file, choose a picture, size same as Sheet1 In the formula bar and replace the entire formula with this formula: =Picture1</SPAN>

INSERT, DEFINE, NAME: Picture2</SPAN> REFERS TO: =OFFSET(PHOTOMstList!</SPAN>$C$2</SPAN>,MATCH(PHOTOSELECTOR!$A$2,PictureList,0)-1,0,1,1)</SPAN>

Sheet2, C2, insert object, create from file, choose a picture, size same as Sheet1 In the formula bar and replace the entire formula with this formula: =Picture2. </SPAN> Then SAME METHOD for further PICTURES.</SPAN>

<TBODY>
</TBODY>

Like I said earlier HOW TO DO THIS PER ROW OF ALL RESULTS, AND HOW TO INTEGRATE THE PHOTO SPREADSHEET PHOTOs~NonMacro2.xls INTO THE EXISTING SPREADSHEET exoftable3~PhotoMacro.xls?

It might occur to me later, like this did! Any ideas on the above?

So far this is the best PHOTO solution, that is if I can get working together with existing car search RESULTS!

 

[srands]

Hi Mick, NEW edit of PHOTO non macro spreadsheet to show RESULTS for 2nd row:

www.1sar.karoo.net/exoftable3~PhotoMacro~2ROWS.xls

FULL DETAILED NOTES BELOW IN FOLLOWING POST

 

[srands]

Hi Mick, NEW edit of PHOTO non macro spreadsheet to show RESULTS for 2nd row:

http://www.1sar.karoo.net/exoftable3~PhotoMacro~2ROWS.xls 1900 kb's It's a big file, even with insert objects/pics blanks as icons, presumably XL is DBLing up, hence once image is transferred I guess it's saved as a full image, hence 2 copies of 1 image, of those transferred.

Also there are some other drawbacks of this method/system but it is a good start that possibly can be edited into something more user friendly and less time consuming. Currently as it is, inevitably the final spreadsheet would involve INSERT, DEFINE, NAME per EVERY PICTURE, of EVERY ROW in the RESULTS table, such as:

Picture1 = OFFSET(PHOTOMstList!$B$2,MATCH(PHOTOSELECTOR!$A$2,PictureList,0)-1,0,1,1)

Picture2 = OFFSET(PHOTOMstList!$C$2,MATCH(PHOTOSELECTOR!$A$2,PictureList,0)-1,0,1,1)

Picture3 = OFFSET(PHOTOMstList!$D$2,MATCH(PHOTOSELECTOR!$A$2,PictureList,0)-1,0,1,1)

Picture4 =OFFSET(PHOTOMstList!$E$2,MATCH(PHOTOSELECTOR!$A$2,PictureList,0)-1,0,1,1)

Picture5 =OFFSET(PHOTOMstList!$F$2,MATCH(PHOTOSELECTOR!$A$2,PictureList,0)-1,0,1,1)

Picture6 =OFFSET(PHOTOMstList!$B$2,MATCH(PHOTOSELECTOR!$A$3,PictureList,0)-1,0,1,1)

Picture7 =OFFSET(PHOTOMstList!$C$2,MATCH(PHOTOSELECTOR!$A$3,PictureList,0)-1,0,1,1)

Picture8 =OFFSET(PHOTOMstList!$D$2,MATCH(PHOTOSELECTOR!$A$3,PictureList,0)-1,0,1,1)

Picture9 =OFFSET(PHOTOMstList!$E$2,MATCH(PHOTOSELECTOR!$A$3,PictureList,0)-1,0,1,1)

Picture10 =OFFSET(PHOTOMstList!$F$2,MATCH(PHOTOSELECTOR!$A$3,PictureList,0)-1,0,1,1)

And to create combo box for each row (A2 & A3, etc) in results a new DATA VALIDATION per each COLUMN A CELL in the RESULTS SPREADSHEET: Data, Validation, Settings, Allow: List. Source: =PictureList

And these DEFINED NAMES aren't as easy to copy & paste like cells!
Perhaps insert, define, name can be replaced with a formula then the $ ABSOLUTE references can be replaced when copied & pasted downwards.

 

[srands]

Hi Mick I've integrated the Picture List method I described into my spreadsheet:
http://www.1sar.karoo.net/exoftable3~PhotoNonMacro.xls 1085 KB's

Looks good, the only edits this really needs is to:
i). Automate the display of the PHOTOs, instead of COMBO BOX in COLUMN K to match ID in COLUMN E.
ANY IDEAS?
Such as SHOW PHOTOS in COMBO BOX/MACRO BUTTON per RESULTS, or PER ROW.

ii). Clear previous search PHOTOS

The ID method is the quickest manual unique identifier I could think of, as bound to be coincidently same makes, models, type & colour, of different cars.

The RESULTS of CAR SEARCHES I've added in PHOTOs for the first 2 rows, the rest I can add later, a small exercise for me!

 

[MickG]

I'm finding all that rather overwhelming, My input was general related to the use of the "ImageLists", So If you managed to find a fomula solution, I'll wish you luck and hope it works out for you.
Regrds Mick

 

[srands]

I'm finding all that rather overwhelming, My input was general related to the use of the "ImageLists", So If you managed to find a fomula solution, I'll wish you luck and hope it works out for you.
Regrds Mick

Hi Mick, thanks. 1 VB code query.

If I moved the COMBO BOXs that were in A2, B2, C2 & D2, moving them respectively to L3, L4, L5 & L6, what VB code edit is needed?

The purpose so the searching COMBO BOX are in a COLUMN instead of a ROW, hence RESULTS follower the HEADER, hence easier to arrange PictureList match from just existing IDs in COLUMN E.

See example file: http://www.1sar.karoo.net/exoftable3~PhotoNonMacroCOLsearchxls.xls 1085 KB's

So what needs changing here?

Option Explicit
Dim Dic As Object
Private Sub ComboBox2_Change()
Dim Rng As Range, Dn As Range, n As Long
Dim Q
Dim Dic1 As Object
Dim col As Integer
Dim Twn As String
Twn = ComboBox1.Value & ComboBox2.Value
With Sheets("Worksheet")
    Set Rng = .Range(.Range("B2"), .Range("A" & Rows.Count).End(xlUp))
End With
For n = 3 To 4
    Set Dic1 = CreateObject("scripting.dictionary")
        Dic1.CompareMode = vbTextCompare
For Each Dn In Rng
    col = IIf(n = 4, 18, n)
    If Dn & Dn(, 2) = Twn Then
        Dic1(Dn(, col).Value) = Empty
    End If
Next
If Dic1.Count > 0 Then
        With ActiveSheet.OLEObjects("Combobox" & n).Object
            .List = Dic1.keys
            .ListIndex = 0
        End With
End If
Next n
End Sub
Private Sub Worksheet_Activate()
Dim Rng As Range, Dn As Range, n As Long
Dim Q
Dim Twn As String
With Sheets("Worksheet")
    Set Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
End With
ReDim Ray(1 To Rng.Count, 1 To 3)
    Set Dic = CreateObject("scripting.dictionary")
        Dic.CompareMode = vbTextCompare
For Each Dn In Rng
    If Not Dic.Exists(Dn.Value) Then
        Ray(1, 1) = Dn(, 2)
        Ray(1, 2) = Dn(, 3)
        Ray(1, 3) = Dn(, 18)
        Dic.Add Dn.Value, Array(Ray, 1)
    Else
        Q = Dic.Item(Dn.Value)
        Q(1) = Q(1) + 1
        Q(0)(Q(1), 1) = Dn(, 2)
        Q(0)(Q(1), 2) = Dn(, 3)
        Q(0)(Q(1), 3) = Dn(, 18)
        Dic.Item(Dn.Value) = Q
    End If
Next
Dim nRay
Dim i As Long
Dim j As Long
Dim Temp As String
nRay = Dic.keys
For i = 0 To UBound(nRay)
    For j = i To UBound(nRay)
        If nRay(j) < nRay(i) Then
            Temp = nRay(i)
            nRay(i) = nRay(j)
            nRay(j) = Temp
        End If
    Next j
Next i
ComboBox1.List = nRay
ComboBox1.ListIndex = 0
End Sub
Private Sub ComboBox1_Change()
Dim DiCB As Object
Dim n As Integer
Dim K
Dim g
ComboBox2.Clear
ComboBox3.Clear
ComboBox4.Clear
Set DiCB = CreateObject("scripting.dictionary")
    DiCB.CompareMode = vbTextCompare
        If Dic.Exists(ComboBox1.Value) Then
            For g = 1 To Dic.Item(ComboBox1.Value)(1)
                DiCB(Dic.Item(ComboBox1.Value)(0)(g, 1)) = Empty
            Next g
        Dim nRay
Dim i As Long
Dim j As Long
Dim Temp As String
nRay = DiCB.keys
For i = 0 To UBound(nRay)
    For j = i To UBound(nRay)
        If nRay(j) < nRay(i) Then
            Temp = nRay(i)
            nRay(i) = nRay(j)
            nRay(j) = Temp
        End If
    Next j
Next i
With ComboBox2
    .List = nRay
    .ListIndex = 0
End With
  End If
End Sub

 

[MickG]

Just moving the boxes will make no difference to them they will still return the same information.

 

[srands]

Just moving the boxes will make no difference to them they will still return the same information.

Hi Mick, isn't this straight forward for you, isn't for me!
If the COMBO BOXES are moved from current position, then they don't work, presume the CELL REF/COLUMN REF/ROW REF must be specific to those CELLS, but in VB/MACRO CODE this is less obvious I guess because COLUMNS are referenced to as NUMBERS, I presume COLUMN A is 1, hence in VB unsure if referring to ROWS and COLUMNS!