2

I have one column where i have values called domain and another column which has values called role.

there can be multiple roles for a domain e.g.

Domain Role
A       XYZ
A       ABC
B       DEF
C       DHG
A       LKJ
B       OIO
C       CND

etc.

I have in same sheet or a different sheet a column containing unique values in column Domain and in the next column i have to display a dropdown based on selection in the Domain column. example if someone selected B in the domain dropdown in one row, then the role column of the same row should display in the drop down only values which are specific to B values in the roles shown above i.e. DEF & OIO

2

Assuming it is acceptable to have the master table sorted and both tables in the same worksheet, the simplest solution entails just a relatively short data validation formula.

I have set up my example worksheet as follows:

Worksheet Screenshot

Enter this formula as the source of the In-cell dropdown List data validation of E2:

=INDEX($B:$B,MATCH(D2,$A:$A,0)):INDEX($B:$B,MATCH(D2,$A:$A,0)+COUNTIF($A:$A,D2)-1)

The beauty of this formula is that it adapts to changes in the master table, including inserting a new row at the top and appending a row after the last one.

The disadvantages are:

  • The master table must be in the same worksheet
  • The master table must be sorted
  • Other tables on the sheet cannot safely intersect the Domain column unless they are guaranteed not to contain values matching the master table's Domain values

More complex solutions exist to overcome all these limitations.


Okay. Just to keep Rajesh S happy, (and prove him wrong ;-) um, no, you don't need an array formula to either generate a unique list, or generate a dependent/filtered list, if the source list is sorted), here is the worksheet updated to include the creation of the unique domains list and the dropdown validation formula in column D that uses it:

Updated Worksheet Screenshot

The normal, non array-entered formula entered into C2 and then filled down is:

=T(INDEX($A:$A,IFERROR(MATCH(C1,$A:$A,0),ROW())+COUNTIF($A:$A,C1)))

The data validation formula for D2 is:

=INDEX($C:$C,MATCH("Unique",$C:$C,0)+1):INDEX($C:$C,MATCH("Unique",$C:$C,0)+ROWS(C:C)-COUNTIF(C:C,"")-1)
  • Friend #robinCTC, how excel will move automatically to D3 to filter Domain B's Role values in E2 (for Drop Down). Don't you think that one more Drop Down is required in D2 to rotate A,B & C, are in fact the criteria to pull the related Role into the Drop Down you have created in E2. – Rajesh S Jun 11 '18 at 9:08
  • Continue,,,,, and the formula will not filter the list since formula needs to be CSE. – Rajesh S Jun 11 '18 at 9:14
  • @RajeshS The OP stated "I have in same sheet or a different sheet a column containing unique values in column Domain". That clearly means they have already worked out how to get the unique Domain values. Whether they are using a dropdown list or have a fixed set of values (like I've shown) is up to them. I've only supplied the requested solution "in the next column i have to display a dropdown based on selection in the Domain column". Nowhere in the question did they ask how to construct a dropdown of unique Domain values. If they need that and request it, I will supply it. – robinCTS Jun 11 '18 at 9:24
  • Btw, I noticed you seem to be using # as in #robinCTC a lot. You need to use an @ as in @RajeshS to notify a user (ping them), that you posted a message, not #. The owner of the post always gets notified. You explicitly need to ping other users. See the details of how it works in this meta post How do comment @replies work?. – robinCTS Jun 11 '18 at 9:36
  • #robin ,, check this one in the formula MATCH(D2,$A:$A,0), currently D2 has A so that it's filtered related roles but, unless it will not B in D2 how could Excel match it with it's Role to filter. And how the Formula Pull B in D2 automatically even if it's unique list of Domain. – Rajesh S Jun 11 '18 at 9:36
0

You need to create Dependent Dropdowns.

enter image description here

Follow these steps:

  • In B304 write haeder, LISTA and put values in rows below as shown in screen shot.
  • Select B305:B307 and Sort the range in Ascending order. Then name this range, "ListA".

N.B. Follow above written steps for ListB & ListC also.

  • Select B304:D304 and name this range, ROLE.

  • Select E304 then go to DATA Tab then click Data Validation.

  • Under Setting Tab for Allow pick LIST and for Source write =Role.

  • Place cell pointer in cell G304 and reach again to Data validation.

  • Write =INDIRECT($E$304) in Source textbox.

Note, then after as soon you select Domain name from first Drop Down Excel will filter related roles in the adjacent Drop down, as you can see in the Screen Shot.

N.B.

  • Adjust the cell address according to your need.

  • If you feel you cab replace LISTA, LISTB & LISTC with A, B & C.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.