Dependant-Cascading drop down list

Question

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!

I don't know if you've tried this contextures.com/xlDataVal02.html I found it easy enough and I think it's exactly what you're trying to achieve — Dani El
@DaniEl Thanks, i've used this method but I was hoping a dynamic list would be more useful... as I could add and remove from the source table in question. — Tony
I'll think about it, if you won't find your answer earlier. @ForwardEd might be onto something. — Dani El
I tried doing it all on the same sheet and wound up with the same problem. it did not like the index command at all! I did notice that if I took the index formula and put it in a cell, as long at the formula was in the same row as the data it would return the name in that row. IF I turned around and used it as an array formula with CSE I could place it anywhere. — Forward Ed

Forward Ed Forward Ed · Accepted Answer · 2016-04-20T15:47:31

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

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:

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

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

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

Dependant-Cascading drop down list

1 Answers

UPDATE