unique-distinct-dependent-lists1-three-columns

Hi

Attach is the Test_unique-distinct-dependent-lists1-three-columns.xls

Need advice under the Remark in the excel :
If E8 = *, want to lookup to Sheet3 and show dropdown in E8 the list of 3 continents ie. Europe, Asia , US

Any advice appreciated . Thanks!

1. What is Excel 1902?

2. What does it say in the green rectangle that appears in the middle of your screen, when you launch Excel?

3. Your sample file is in the elderly .xls format, which went out when Excel 2007 was launched.... 13 years ago. Are you still using a pre-Excel 2007 version? If your Excel version is Excel 2007+... why use .xls?

OK. Please amend your profile to show 365 as the version.

I replaced all the formulae in Sheet2 with O365 dynamic arrays:

A2:
=UNIQUE(Sheet1!A2:A11)

B2:
=FILTER(Sheet1!B2:B11,Sheet1!A2:A11=Sheet1!$E$2)

C2:
=LET(a,FILTER(Sheet1!C2:C11,((Sheet1!B2:B11=Sheet1!E5)*(Sheet1!A2:A11=Sheet1!E2))),IF(a="*",UNIQUE(FILTER(Sheet1!C2:C11,Sheet1!C2:C11<>"*")),a))

see sheet. They are NORMAL formulae (no CTRL-Shift Enter needed in O365)

... and (hopefully!!) solution provided...

I think your expected answers for 1030/HDD should have been all 3.... not just Asia (as stated in your last attachment).

Try current version attached here.

You have me COMPLETELY confused. My DDs return EXACTLY what you are showing. You made a comment about the DDs being horizontal... then just move them.


PLEASE DO NOT post changes /clarifications in your original sheet. Make them in MINE. I am not intereestd in looking at your original sheet any more. Focus on what is in mine.

Maybe YOU misunderstand what DDs do. If you change something at the 3rd level, the values in the earlier levels WILL NOT change without VBA.

If this is not what you want tell me EXACTLy which inputs are giving you the WRONG outputs and what the correct outputs should be.

I am beginning to think that you do not know how dropdown boxes work...

IF you have all 3 DDs populated and you change the first one, nothing changes in the 2nd and 3rd ones. YOU have to reselect the 2nd and 3rd DDs, when they WILL show the correct options.


If you want DD 2 & 3 to empty if you change DD1... you need VBA.


For 1010, you expect the ONLY choices in column F to be GC & SC. Look at your data table. Row 4. Yellow. 1010 & MB is listed. Therefore when you select 1010 you get all 3 possible options: GC, SC & MB. If you DO NOT want MB... why is it listed in row 4???

For 1020, same argument. if you DO NOT want KB and mB to be DD choices, WHY are they listed in rows 7 & 8?

Similarly for 1030... it ONLY shows HD in DD2 and shows all 3 in DD3. See the 3 images, as proof.

Last image from 1030 after GC selected.

If you want to have the combination of 1010/HDD/USA then you need to include it as an allowable option in the data table. If it is NOT included in the allowable options, you will not be able to select it!

See yellow row in file. Either use an asterisk in column C, or US... if the only permitted country is US.

If you want it to be available for 1020 as well, you have to include it as an allowable option.

You did not make that clear. Give me 5 minutes.

Try it now.

Pl see file .
sheet1 is with worksheet events code.
Worksheet_Selectionchange and Worksheet_change events are used

You asked for:
But I want DD 2 & 3 to empty if you change DD1..
I did that for you.

Now you say:
But when DD1 select new value, DD2 has no value and DD3 also no value since no value is selected in DD2. You wanted DD2 and DD3 to be blank if you change DD1. Now you don't want them to be blank.

Make your mind up!!

If you change DD1, DD2 and DD3 go blank to prevent incorrect choices. BUT you have to make NEW choices in DD2 and DD3, which will reflect the value in DD1...

The attachment at my last post works perfectly.

1) E1=1010 and F1=Hard Disc ( then "*" is third column ) then Drop down is from sheet 3. Pl see file.

2) There is no restriction in the number of rows in A column. Depending on data up to last row code works.

File is available in previous post.

Originally Posted by Grace Tan

Hi Murthy
1) Yes this is working - E1=1010 and F1=Hard Disc ( then "*" is third column ) then Drop down is from sheet 3

Pl post excel workbook not images and word files.
Pl see file.For * in second column, then drop down is from Sheet3, it is working.

your quiries are already solved in my previous posted file. Pl check.

in which cell text of of > 256 characters is required. Because we have o in round about route to get text length of >256 .
Pl see file. I have given Example.

Validation list H7:H10
Text list I7:I10
Validated cells A1:A3
Required text cells (Result cells) B1:B3

Formula in B1 then copied down.

=IFERROR(INDEX($I$7:$I$10,MATCH($A1,$H$7:$H$10,0)),"")

Pl see my previous post and attached file. It may help.

Pl see file.
See Range A16:C18

Restriction of 256 is for the text in single cell and not for the total length of the list.
Pl see file. I added more rows and it is working.

I am not finding any problem to save or open file even after adding the additional list.
Pl copy the file to pen drive and try on another PC.

Pl see file.
I have changed the code. No modification required.

Please Login or Register  to view this content.

Select row 11 column A : CLOUD_GENERAL, I am getting in E2 drop down from Sheet3 as there is star in B11. Further F2 will not have any drop down.

I have changed code. Pl see file.

Welcome. Pl mark the thread solved.

Code is revised. it covers the entire columns E, F and G.
Pl find the code in Work sheet event.