@
vijay.vizzu /@bhineet,
To get such dependent drop down list which are dynamic in nature (i.e. can expand or retract) we have to get the list and sub lists through formulas. Now there can be two conditions in data:
1. Unique data
2. Duplicate Data
If the case is no. 1 than there is no requirement of formula and you can directly the list to a named range and then in Data Validation List source you can refer this named range or you can directly refer the range.
But, if there are duplicates then we need to extract unique data, which can be done through either advanced filter (if this is a onetime activity) or through formulas (if the list is dynamic)
Now in this problem, both Column A & Column B has duplicate data. What I did, I extract unique data of Column A through advanced filter first (this will reduce the load on worksheet calculation when you change the dropdown & more over the no. of options for column A data seems to be fixed).
After this I created a named range for this first list, and created our first drop down by pointing the source to this named range.
Now on selection from this drop down, I extracted the values from column B which has same data in column A through below array formula.
Code:
=IFERROR(INDEX($B$2:$B$81,SMALL(IF(FREQUENCY(IFERROR(MATCH(IF(Sheet1!$A$2=$A$2:$A$81,$B$2:$B$81),$B$2:$B$81,0),""),ROW(Tag_Addition!$B$2:$B$81)-ROW(Tag_Addition!$B$2)+1),ROW(Tag_Addition!$B$2:$B$81)-ROW(Tag_Addition!$B$2)+1),ROWS(J$4:J4))),"-")
Now this formula is a standard INDEX formulas which will give data from the array
$B$2:$B$81, based on the ROW number passed by SMALL function.
Now in SMALL function we need to create an array of row number pertaining to unique values of column B, which is done through
Code:
IF(FREQUENCY(IFERROR(MATCH(IF(Sheet1!$A$2=$A$2:$A$81,$B$2:$B$81),$B$2:$B$81,0),""),ROW(Tag_Addition!$B$2:$B$81)-ROW(Tag_Addition!$B$2)+1),ROW(Tag_Addition!$B$2:$B$81)-ROW(Tag_Addition!$B$2)+1)
So IF function will do a logical test by FREQUENCY function, frequency function has two argument data array & bin array. Data array is formed by MATCH function by matching values of column B which are there for our selection in first list to column B. This will deliver either the ordinal position or an error. Now error are suppressed by IFERROR FUNCTION. So we will get some number like {1;2;3;7;7;7;8;9;4;3;3;3;””;””;””…..}. This will be our data array, now for bin array ROW(Tag_Addition!$B$2:$B$81)-ROW(Tag_Addition!$B$2)+1 is used which will create array like{1;2;3;4;5;6;7;…..;80}. Now FREQUENCY function will give the frequency for first position of data and for duplicate it do 0. So finally we will get an array like {1;1;4;3;0;0;1;1;1;0;0;0….} in logical expression of IF function. Now IF function treats any non-zero number as TRUE and 0 as FALSE, so it will again assign a row number where there is a positive value with the same construct ROW(Tag_Addition!$B$2:$B$81)-ROW(Tag_Addition!$B$2)+1.
Than we had array in our SMALL function, now ROWS(J$4:J4) will generate number like 1;2;3…. As we drag the formula down. So SMALL function will give 1st row number, than 2nd and so on. And Index will give the value from 2nd list based on row number.
Finally, the whole function is surrounded by IFERROR to remove error when all the data is extracted and replace by -.
SO, now we have both the list, now we have to form our second named range through a formula which is again dynamic is nature as this second list size will vary. This is done through OFFSET function as below:
=OFFSET(Tag_Addition!$J$4,,,COUNTIF(Tag_Addition!$J$4:$J$13,"<>-")-1)
SO, this will give the list from J4 (from this cell we had our formula started) till it encounter first - .
And finally passing this named formula in second data validation source.
Hope this will clear your doubt, if not than write back, I will try my level best to make it clear.
Regards,
