Validation Dependent List

[harinsh]

Hi Team,

I am looking for depended list in excel validation, can anyone please assist me on this...what formula should I use in validation list...

Input:
[TABLE="width: 128"]
<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]Grade[/TD]
[TD="width: 64"]Products[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Aproduct[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Bproduct[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Cproduct[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Aproduct[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Aproduct[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Aproduct[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Aproduct[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Bproduct[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Cproduct[/TD]
[/TR]
</tbody>[/TABLE]

Output:
When I select in cell A2 as grade "A" then I should only get list in B2 cell those products against to grade "A". I created the validation however, could not find what formula should I use for B2 validation field in the validation list.

Thank you,

 

[istiasztalos]

Hi,

I needed the same a while back but without VBA I could only come up with a solution that works if your input table is sorted by Grade. Either the original one or if not you'll need a backup sheet where you sort the table with a formula

Once sorted, you can use this offset, wherein the named range 'grades' is your list of grades in your input table and 'header' is the cell on top of your grade list
=OFFSET(header,MATCH(A2,grades,0),1,COUNTIF(grades,A2),1)

 

[harinsh]

Thanks for your response....can't we achieve this without sort? I have huge data which I need keep add at bottom everyday !!

 

[istiasztalos]

To my knowledge you can't set validation list using an array, I tried, but only takes the first value; and when you try to force feed Excel an array it actually gives an error message stating that you cannot define validation list with an array....

If it's not an option to create an array from the results of a search/filter and set it as criteria, then we can only use an actual range reference, either with offset, indirect(also doesn't seem to work with non contiguous ranges) or range name (default or user named).

Someone smarter may offer another solution, but I think you'll need a back-end table, maybe on hidden sheet, where you dynamically create with formulas a sorted version of the data table. (extending your table daily is not a problem, you can use either a data table or setup manually an offset formula based named range that always extends itself)

 

[harinsh]

Thank you for your suggestion and time...