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.

 

[srands]

Mick, NEW version of spreadsheet, added "A" feature, 1 PHOTO per car in the source worksheet, 1 PHOTO displays in the RESULTS.
Currently I have succeeded in adding 1 PHOTO per CAR. However need 5 photos+ per car really.

http://www.1sar.karoo.net/exoftable3~PhotoMacro.xls

The PHOTO FEATURE in results is a VB code, module 3, any idea if can be edited to include 1+ PHOTO?
Or another VB or a simpler formula?

To go into more detail about the PHOTO feature, Master Photos are in COLUMN AF per ROW</SPAN> of WorkSheet</SPAN>.
PHOTOs are displayed in RESULTS</SPAN> in</SPAN> COLUMN F</SPAN> if that car is selected, for example select: PEUGEOT, 206, HDI, SILVER, left click GET IMAGES.

Perhaps the VB code in module 3 can be edited to simply add more photos by INSERTING COLUMNS from COL AG onwards in WORKSHEET.</SPAN>

The two spreadsheets:

i). THE MASTER DATA LIST of ALL CARS: WorkSheet</SPAN>
A simple list of cars.

ii). THE SORTED DATA: RESULTS</SPAN>
A sort is performed depending on combo boxs selected: CAR, MODEL, TYPE & COLOUR.

http://www.1sar.karoo.net/exoftable3~PhotoMacro.xls

VB code is in module 3 is as follows for 1 PHOTO per each car, hence needs editing for more then 1 photo per car in RESULTS</SPAN>:

Sub GetImages()
u = Sheets("WorkSheet").Range("AE65536").End(xlUp).Row
For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 4) = "Pict" Then
shp.Delete
End If
Next
 

111
For ii = 33 To 999
If Cells(ii, 1) = "" Then Exit For
Next ii
K = ii - 1
For i = 6 To K
For j = 2 To u
If Sheets("Results").Cells(i, 5) = Sheets("WorkSheet").Cells(j, 31) Then
Sheets("WorkSheet").Cells(j, 32).Copy Sheets("Results").Cells(i, 6)
End If
Next j
Next i
End Sub

</SPAN>


CROSSTHREADS:</SPAN>
http://www.mrexcel.com/forum/excel-questions/674433-displaying-multiple-many-photos-sorted-results-opposite-each-other.html#post3340962</SPAN>

 

[MickG]

Have you considered using an "ImageList", this is a Control from the "Control ToolBox" menu.
Yous can Physically add a number of pictures from a File to it. then those pictures would be held In the Image list for that sheet/Workbook.
YOu could have one image list for each Make of car, or all the Makes in one Image List.You can select (Vis code) the images from a "ImageList" in the Active sheet or any other.
You would also need in your "Photo" columns a number of image controls (Picture frames)to load the pictures into, then as you selected the cars from the comboboxes the Image Controls would be filled, via a bit of code that selected the right images.
What do you think.

 

[srands]

Have you considered using an "ImageList", this is a Control from the "Control ToolBox" menu.Yous can Physically add a number of pictures from a File to it. then those pictures would be held In the Image list for that sheet/Workbook. YOu could have one image list for each Make of car, or all the Makes in one Image List.You can select (Vis code) the images from a "ImageList" in the Active sheet or any other. You would also need in your "Photo" columns a number of image controls (Picture frames)to load the pictures into, then as you selected the cars from the comboboxes the Image Controls would be filled, via a bit of code that selected the right images.What do you think.

Hi Mick, I did not know about this FEATURE! What you describe sounds great. Can you add in? http://www.1sar.karoo.net/exoftable3~PhotoMacro.xls 175 KB's

I have tried, in other words I do not know how to:
~ Insert IMAGE CONTROL PHOTOS per row for EACH CAR for EACH PHOTO, say upto 5 PHOTOS PER ROW/CAR (I guess this would dictate PHOTO SIZE) in WorkSheet, hence that is to make PHOTOs specific to certain ROW/CAR. Also, can these be simply copied & pasted downwards for each row? Or edit once to make CELL REFERENCES correct, then leave alone!
~ I don't know what you mean by VIS code, or where this is accessed from, is this within properties of a toolbar, or referenced in FORMULA?
~ Since the DATA will change, I will add new rows/cars, with PHOTOS of that specific car, so ideally when adding PHOTOS preferably the data sort of the combo boxes will automatically identify and display the correct PHOTO(s), without having to recode the combo box for each new row/car.
~ COMBO BOX coding for CELL E2, to show correct photos, for car(s) that meet criteria, in RESULTS in COLUMNS F+.

Not sure how to do it all though, after INSERTING PICTURES into "AN" IMAGELIST, on my version Excel 2003! I added from TOOLBOX, CONTROL TOOLBOX, More Controls, Microsoft ImageList Control 6.0 (SP4), then left click to insert on spreadsheet, then ImageListCtrl Object, Properties, Images, Insert Picture, select pictures, OK. What to do after that, to make PHOTOS specific to a CERTAIN ROW/CAR, I am unsure of!
I realise each picture is given an INDEX NUMBER from 1 onwards, and the fields KEY and TAG can be edited. How this is used, I don't know, perhaps their relevance will be more obvious later. Maybe you can make their uses more obvious to me!

 

[srands]

Have you considered using an "ImageList".

Hi Mick I've created some working examples using Image Lists, however these versions are currently just 1 COLUMN:
Master sheet of CARS with 5 PHOTOS PER CAR in 1st Sheet.
Select desired CAR for upto 5 PHOTOs per car to be displayed in 2nd Sheet.

http://www.1sar.karoo.net/PHOTOs~NonMacro2.xls 805 KB's

</SPAN>http://www.1sar.karoo.net/PHOTOs~Macro2.xls 786 KB's

In RESULTS/PHOTOselector page how do I edit for ALL 5 PHOTOS for each CAR selected (Either by CODE or COMBOBOX), hence MULTIPLE COLUMNS and per ROW. </SPAN>
In other words 5 COLUMNS: PHOTO1, PHOTO2, PHOTO3, PHOTO4, PHOTO5 and then PER ROW</SPAN>

In FORMULA terminology speak currently the row by row of 1 COLUMN is B:B.
I guess the equivalent search I want is for example starting from B2:F2, and EACH ROW of CARS selected by either:</SPAN>
Enter code then MACRO BUTTON “GET PHOTOS” (MACRO TYPE) </SPAN>
or </SPAN>
Combobox (NON-MACRO TYPE).</SPAN>

My edits are below:</SPAN>

i). Macro type </SPAN>http://www.1sar.karoo.net/PHOTOs~Macro2.xls 786 KB's

The macro version looks more plausible then the non-macro.</SPAN>

Unsure of VB code for COLUMNS way instead or ROWS way, then when edited for all of the 5 PHOTO COLUMNS, for EACH ROW a CODE specific to a car, is entered in COLUMN A of RESULTS/PHOTOSELECTOR.</SPAN>

In other words how to include COLUMNS B to F per EACH ROW? </SPAN>
As well as COLUMN A, instead of just COLUMN A. </SPAN>

Sub GetImages()
u = Sheets("WorkSheet").Range("A65536").End(xlUp).Row
For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 4) = "Pict" Then
shp.Delete
End If
Next
111
For ii = 2 To 999
If Cells(ii, 1) = "" Then Exit For
Next ii
k = ii - 1
For i = 2 To k
For j = 2 To u
If Sheets("Results").Cells(i, 1) = Sheets("WorkSheet").Cells(j, 1) Then
Sheets("WorkSheet").Cells(j, 2).Copy Sheets("Results").Cells(i, 2)
End If
Next j
Next i
End Sub

ii). Non-macro type</SPAN> http://www.1sar.karoo.net/PHOTOs~NonMacro2.xls 805 KB's

The non-macro consisting of lists maybe possible for multiple photos, but how to edit the PHOTOSELECTOR sheet for display multiple columns for that SPECIFIC CAR, and then PER ROW for EACH CAR (of Combo Boxs selected) I don't know.</SPAN>

(Defined Name & Data Validation)

I created this spreadsheet as follows:
CREATE LIST WITH PHOTOS: Column A is DESCRIPTION, Column B is PHOTOs

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

INSERT, DEFINE, NAME
NAME: Picture
REFERS TO: =OFFSET(Sheet1!$B$2,MATCH(Sheet2!$A$2,PictureList,0)-1,0,1,1)

Sheet2, B2, insert object, create from file, choose a picture, RE-size same as Sheet1 photos.

Sheet2, A2, Data, Validation, Settings, Allow: List.
Source: =PictureList

Then place mouse over CELL A2 for combo box to appear, then select choice, and PHOTO should appear.</SPAN>

CROSSTHREADS:</SPAN>
http://www.mrexcel.com/forum/excel-questions/674871-picture-selector-instead-row-columns-way-then-per-row.html#post3342982</SPAN>

 

[MickG]

That's a lot of details to look through.
Attached is a link to your returned Photo File.
https://www.box.com/s/bm24w5ubnr813r1acbku
I have added a "ImageList " and some Pictures for example.
This example only shows one picture (in Image Control) at a time for a selected model , but it could be altered for All images per model/ click.
You will see further infor on the sheet.
NB:- if you play with the code you may need to Save the file then reopen to enable the "Worksheet_Open"Event to work.

 

[srands]

That's a lot of details to look through.
This example only shows one picture (in Image Control) at a time for a selected model , but it could be altered for All images per model/ click.

Hi Mick, I understand your edit. However keep the PHOTOs in WorkSheet, and in RESULTS display 5 IMAGES per car/macro click. Can that be done?

I edited the 3 workbooks to make space for this method of 5 photos in Worksheet, for choices to be displayed in RESULTS:

The 1st spreadsheet has the full car list data & 5 photos for 2 examples, Peugeot 306 row 3 & Peugeot 206 row 10.

2nd & 3rd spreadsheets are just car model & 5 photos for 2 examples, Peugeot 306 row 2 & Peugeot 206 row 3:

1. [URL]http://www.1sar.karoo.net/exoftable3~PhotoMacro.xls[/URL] 907 KB's

</SPAN>2. http://www.1sar.karoo.net/PHOTOs~Macro2.xls 786 KB's

3. http://www.1sar.karoo.net/PHOTOs~NonMacro2.xls 798 KB's

In other words It would be easier for the photos to remain in WorkSheet, as having a seperate IMAGEList in RESULTS I simply won't remember what car photo I need to add, or when editing what PHOTOs to remove, and where are they, and what's their name, again!

 

[MickG]

Please confirm/ Explain
Q(1) Are you happy to have the images stored in sheet "Worksheet" in seperate "Imagelists" for each Make/Model.
Q(2) Do you need to see these pictures in the sheet "Worksheet" other than by accessing each "ImageList"
Q(3) Do you want to see more than one picture at a time in sheet "Results"
Q(4) Do you want to show the Picture/s as you select cars from the related Comboboxes.

 

[srands]

Please confirm/ Explain
Q(1) Are you happy to have the images stored in sheet "Worksheet" in seperate "Imagelists" for each Make/Model.
Q(2) Do you need to see these pictures in the sheet "Worksheet" other than by accessing each "ImageList"
Q(3) Do you want to see more than one picture at a time in sheet "Results"
Q(4) Do you want to show the Picture/s as you select cars from the related Comboboxes.

Q1. No. Because I wouldn't be sure what row they refer to AFTER I've add them, then they no longer become relevant, then I would be unsure which photos to DELETE.
Q2. Yes. I add them for that specific car.
Q3. Yes.
Q4. Yes.

 

[MickG]

Are you happy to use "ImageLists".
You can add an imageList to each unique Car/Model in sheet "Worksheet" at the first instance of the row for that unique Car/Model.
So each "ImageList" and its name refers a specific Car/Model , that would ensure you would know what Imagelist belonged to what Car/Model and be able to Edit them easily.
You could display the Images (for Ref) in sheet "Worksheet" Using an "Imaglist" and "Image" controls, The picture would be fro ref only and reflect what was in the "ImageList".
If you just place Pictures the sheet as per you previous files, I'm not sure how you transfer them to the "Results" sheet and it sounds utimately far more complicated.

 

[srands]

Are you happy to use "ImageLists".
You can add an imageList to each unique Car/Model in sheet "Worksheet" at the first instance of the row for that unique Car/Model.
So each "ImageList" and its name refers a specific Car/Model , that would ensure you would know what Imagelist belonged to what Car/Model and be able to Edit them easily.
You could display the Images (for Ref) in sheet "Worksheet" Using an "Imaglist" and "Image" controls, The picture would be fro ref only and reflect what was in the "ImageList".
If you just place Pictures the sheet as per you previous files, I'm not sure how you transfer them to the "Results" sheet and it sounds utimately far more complicated.

Hi Mick. Yes.

An IMAGElist per each row/car in WorkSheet, sounds plausible, as it would be obvious what photos are relevant to which row/car. The images reflections are a bit small though, can you make them bigger? You know re-size them, or is it just 1 size?

What I suggested would be the most convenient, currently how to do that is something I don't know, and as yet I haven't seen any formula/vb/imagelist examples that do this.