Search code examples
excelexcel-formulacascadingdropdown

Dependant-Cascading drop down list

I'm having a issue in regards to a dependant drop down list. I've set up a table of data(sheet1):

       A    |    B    |   C
1   Manager | Cashier | Driver
2   Joe     | Sarah   | Tom
3   Sophie  | James   | Anthony

I have 2 ranges:
1) to denote the table name = RoleTable
2) $A$1:$C$1 = RoleHeaders

In Sheet2 I have set a cell ($A1) as the range "Role", I have now put data validation on the same cell =RoleHeaders as a list)(drop down).

I have set another Range = 'RoleName' and set the following formula:

RoleName=INDEX(RoleTable,,MATCH(Role,RoleHeaders,0))

I have made B2=RoleName. Now this works fine when I have just one list for the roles, but as soon as I insert validation into cell A2, as role = A1 the dependant list won't work and is based on the value in A1.

I've searched the net and seen so many different ways to do this...is there a simple way just to edit my current forumla or setup?

Thanks!

Solution

  • Try adding the sheet name to your name ranges.

    Change your RoleTable from $A$1:$C$3 to Sheet1!$A$1

    and then change your index formula to an offset formula

    =OFFSET(RoleTable,1,MATCH(Role,RoleHeaders,0)-1,CHOOSE(MATCH(Role,RoleHeaders,0),COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$B:$B),COUNTA(Sheet1!$C:$C),COUNTA(Sheet1!$D:$D),COUNT(Sheet1!$E:$E))-1,1)

    You should be able to do all that through the Name Manger on the Formulas ribbon.

    I just caught the part where you were trying to make it dynamic. Instead of 2 in the offset formula, you could replace that with a CountA(C:C). my problem right now is I am trying to figure out how to generate the select the whole column reference when you are dealing with the number of the column.

    You could do it with indirect and CHR() but I was hoping there was something cleaner...Still have to look at the address formula.

    UPDATE

    Here is a screen shot of my Name manager, note I was testing with data on sheet6 and calling from sheet7. This should be the same as your sheet1 and sheet 2

    Name Manager

    This is an copy of the cut off offset formula. If should be the same as the one above with the exception that it is referencing sheet6 instead of sheet1:

    =OFFSET(RoleTable,1,MATCH(Role,RoleHeaders,0)-1,CHOOSE(MATCH(Role,RoleHeaders,0),COUNTA(Sheet6!$A:$A),COUNTA(Sheet6!$B:$B),COUNTA(Sheet6!$C:$C),COUNTA(Sheet6!$D:$D),COUNTA(Sheet6!$E:$E))-1,1)

    That will handle up to 5 columns of source pulldown items. I think choose has a limit of 6 so you could add another counta. Seems I had count instead of counta for the 5 choice at column E:E. I just corrected that in the posted version.

    This is the test data I had on my sheet6 which would be your sheet1: Test Data

    Here is an image of the choosing of Role in sheet7 (your sheet2) cell A1. notice there is no formula in the cell.

    Role pict

    Here we see you dependent dynamic drop down list in action only showing 2 entries.

    Manager pick

    And here we can see the list has expanded to catch an added name to the drivers list.

    Driver Pick