Drop down list: unique value's, values based on that choice, search values based on choice

[Stef11]

Hello,

Is there someone who could help me with a solution or a direction?

Here an example of my dataset.
(The table is in real much larger with more colums and types.)

Table.PNG


I created drop down lists with "Data Validation" + "Allow" + "List" and than i selected one of the rows in my table.


2 problems.PNG


3rd.PNG

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[jolivanes]

Ken je hier iets mee?
Maybe you can change this to suit your needs.
Next time maybe attach a workbook instead of pictures.

[Stef11]

Thanks Jolivanes for your file.
At this moment I don't understand your example enough to know how to use it on my situation.
Mayby I can figure it out later this week.

[Stef11]

My example workbook attached:

[JeteMc]

Dependent drop downs require a bit of preparation.
The list in H2:H7 is populated using:

=IFERROR(IF(MATCH(0,INDEX(COUNTIF(H$1:H1,Tabel1[Serie]),,),)>COUNTA(Tabel1[Serie]),"",INDEX(Tabel1[Serie],MATCH(0,INDEX(COUNTIF(H$1:H1,Tabel1[Serie]),,),))),"")

Copy (Ctrl + c) H2:H7 and paste as values > transpose (Ctrl + Alt + v) into cell J1
J2:L11 are populated using:

=IFERROR(INDEX(Tabel1[[Type]:[Type]],AGGREGATE(15,6,(ROW(Tabel1[[Type]:[Type]])-ROW(Tabel1[[#Headers],[Type]]))/(Tabel1[[Serie]:[Serie]]=J$1),ROWS(A$1:A1))),"")

Select J2:J7, Press the Ctrl key and select K2:K4, press the Ctrl key again and select L2:L5
While the three ranges are select press the Ctrl, Shift and F3 keys
In the Create Names from Selection dialog box be sure that Top row is checked and select OK
The data validation for cell F2 is: =OFFSET(H1,1,0,SUMPRODUCT(--(H2:H7<>"")),1)
The data validation for cell F3 is: =INDIRECT(F2)
The formula for F6 is: =INDEX(Tabel1[Type],AGGREGATE(14,6,(ROW(Tabel1[Serie])-ROW(Tabel1[#Headers]))/(Tabel1[Serie]=F2)/(Tabel1[Weight]<=F5),1))
Let us know if you have any questions.

[jeffreybrown]

Maybe you could also use in the data validation for F3...

Create a name >> My_Type =OFFSET(Tabel1[[#Headers],[Serie]],MATCH(QUESTION!$F$2,QUESTION!$A:$A,0)-1,1,COUNTIF(QUESTION!$A:$A,QUESTION!$F$2))

Then in the data validation on F3 use My_Type as the list source. Now you don't need the stuff in column J:L. Just a thought.