Hi All,
I am trying to achieve the following, using the table below as expample:
Set up in D1 a validation list (dropdown) that pulls the right values from column B, based on the filter on A (from user choice in C1).
E.g. if user inputs "Joe" in C1, then the validation list in D1 should have {apple, corn, pear}.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]apple[/TD]
[TD][user enters column A value here][/TD]
[TD][validation list with filtered column B values here--depending on user input on C1][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]corn[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]pear[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]corn[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Question: what should be the formula for the validation list in D1, taking into account that it seems that array formulas do not work there (i.e. index/match)
Finally, I do not want to have separate columns or sheets to precalculate stuff. Also, order is not significant.
I tried I am busting my head but cannot make it work...
Thanks!
I am trying to achieve the following, using the table below as expample:
Set up in D1 a validation list (dropdown) that pulls the right values from column B, based on the filter on A (from user choice in C1).
E.g. if user inputs "Joe" in C1, then the validation list in D1 should have {apple, corn, pear}.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]apple[/TD]
[TD][user enters column A value here][/TD]
[TD][validation list with filtered column B values here--depending on user input on C1][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]corn[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]pear[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]corn[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Question: what should be the formula for the validation list in D1, taking into account that it seems that array formulas do not work there (i.e. index/match)
Finally, I do not want to have separate columns or sheets to precalculate stuff. Also, order is not significant.
I tried I am busting my head but cannot make it work...
Thanks!