Forum Discussion
Solved
Need help creating a dynamic list on excel 2016
Hi,
I am currently using Excel 2016 and I need help to get a dynamic list based on an input.
for instance, I have a dataset with 4 columns out of which I need to give an input (which is a value of col2) for which I should get a data validation list of all values in col3 which has the input value in col2.
| col1 | col2 | col3 | col4 | |||
| 1 | A | hi | 23 | |||
| 2 | B | bye | 65 | |||
| 3 | C | hello | 45 | Input | DV list | |
| 4 | C | hey | 23 | B | bye | |
| 5 | B | whats up | 12 | whats up | ||
| 6 | A | gtg | 3 | ttyl | ||
| 7 | B | ttyl | 3 | cyl | ||
| 8 | C | cusoon | 1263 | |||
| 9 | B | cyl | 0.23 | |||
| 10 | A | whatsup | 0.0262 | |||
can anyone help me get the best way to do this?
=IFERROR(INDEX($C$2:$C$11,SMALL(IF($B$2:$B$11=$F$5,ROW($A$2:$A$11)-1),ROW(E1))),"")Maybe with this formula. Enter the formula with ctrl+shift+enter as you don't work with Office365 or 2021.
- Tik tok account settings can help me
A PivotTable with Col2 as a filter and Col3 as a row would be a simple solution if you're looking to create the DV List column from values found in Col3 as filtered by the selector Input which is populated with distinct values from Col2.
Below the value A is selected in the PivotTable filter, Input producing the PivotTable row, DV list. The list is then used to validate the data in cell I7, Validated Selection.
Not sure if this is exactly what you wanted to do. Hope it helps.
If to avoid array formula and make it bit dynamic
=IFERROR( INDEX( $C$2:INDEX(C:C, COUNTA(C:C) ), AGGREGATE(15, 6, 1/ ( $B$2:INDEX(B:B, COUNTA(B:B) ) =$F$5) * $A$2:INDEX(A:A, COUNTA(A:A) ), ROW()-ROW($H$4) ) ), "" )- Thanks a ton! Would you be able to explain briefly how this formula works?
We use dynamic ranges here, e.g.
$C$2:INDEX(C:C, COUNTA(C:C) )
is equivalent of $C$2:$C$11 for your sample. If you add more rows there is no need to re-write the formula.
AGGREGATE() works natively with the arrays, thus you don't need to use Ctrl+Shift+Enter. With it we take Nth (4th parameter ROW()-ROW($H$4) ) smallest (first parameter 15) value from the col1 ignoring errors (second parameter 6). Errors are generated if col2 is not equal to input, when
1/( $B$2:INDEX(B:B, COUNTA(B:B) ) =$F$5) generates division on zero error, otherwise returns 1. That is multiplied on col1 index. Instead of col1 we may use ROW() on range.
Thus function returns 2 in first cell, 5 in next, etc. We wrap by INDEX to return value from col3 for that position and all together is wrapped by IFERROR() to return empty string if position is not found.
See the attached example.
=IFERROR(INDEX($C$2:$C$11,SMALL(IF($B$2:$B$11=$F$5,ROW($A$2:$A$11)-1),ROW(E1))),"")Maybe with this formula. Enter the formula with ctrl+shift+enter as you don't work with Office365 or 2021.
Thank you so much, tbh, That was helpful.
however, I am not able to use that formula with data validation.
is there anyway I can get this to work with datavalidation?
Data validation list works with reference. First you need to create the list at any place and when use it in data validation.
