Cascading Drop Down Lists in Data Validation

[Lexi_2206]

I have searched all the threads and articles I could and still can't figure out what I'm doing wrong to get my last drop down list to properly execute.

What am I doing wrong? PLEASE HELP!!

Column C is the first drop down/Parent Column (COURSE) =OFFSET(Sheet2!A1,1,0,COUNTA(Sheet2!$A:$A)-1,1)
Column D is the first child drop down (DAY) =OFFSET(Sheet2!$B$1,MATCH(C2,Sheet2!$B:$B,0)-1,1,COUNTIF(Sheet2!$B:$B,C2),1)
Column E (TIME) =OFFSET(Sheet2!$c$1,MATCH(d2,Sheet2!$c:$c,0)-1,1,COUNTIF(Sheet2!$c:$c,d2),1)
I have been able to get all drop downs displayed properly except for Column E. The closest I have gotten so far is having the drop down give me a list of times for ALL the courses given on that particular date; not just the course selected in Column C.

SHEET2

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]COLUMN A
[/TD]
[TD]COLUMN B
[/TD]
[TD]COLUMN C
[/TD]
[TD]COLUMN D
[/TD]
[/TR]
[TR]
[TD]"PARENT"
[/TD]
[TD]"CHILD"
[/TD]
[TD]"CHILD"
[/TD]
[TD]"CHILD"
[/TD]
[/TR]
[TR]
[TD]English
[/TD]
[TD]English
[/TD]
[TD]4/18/2017
[/TD]
[TD]0800-1100
[/TD]
[/TR]
[TR]
[TD]Math
[/TD]
[TD]English
[/TD]
[TD]4/20/2017
[/TD]
[TD]1200-1400
[/TD]
[/TR]
[TR]
[TD]Sciene
[/TD]
[TD]English
[/TD]
[TD]4/18/2017
[/TD]
[TD]1200-1400
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Math
[/TD]
[TD]4/20/2017
[/TD]
[TD]0800-1100
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Math
[/TD]
[TD]4/24/2017
[/TD]
[TD]1200-1400
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Math
[/TD]
[TD]4/18/2017
[/TD]
[TD]1200-1400
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Science
[/TD]
[TD]4/24/2017
[/TD]
[TD]1200-1400
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Science
[/TD]
[TD]4/20/2017
[/TD]
[TD]0800-1100
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Science
[/TD]
[TD]4/18/2017
[/TD]
[TD]1200-1400
[/TD]
[/TR]
</tbody>[/TABLE]


SHEET1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[TD]Column E
[/TD]
[/TR]
[TR]
[TD]Last Name
[/TD]
[TD]First Name
[/TD]
[TD]Course
[/TD]
[TD]Day
[/TD]
[TD]Time
[/TD]
[/TR]
[TR]
[TD]Bob
[/TD]
[TD]Billy
[/TD]
[TD]Math
[/TD]
[TD]4/24/2017
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ann
[/TD]
[TD]Joe
[/TD]
[TD]English
[/TD]
[TD]4/18/2017
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe this...

First of all...
Sort the columns B, C and D of Sheet 2 by CHILD1 and by CHILD2 (add a new level)

You get this in Sheet2

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

PARENT​

[/TD]
[TD]

CHILD1​

[/TD]
[TD]

CHILD2​

[/TD]
[TD]

CHILD3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

English​

[/TD]
[TD]

English​

[/TD]
[TD]

18/04/2017​

[/TD]
[TD]

0800-1100
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Math​

[/TD]
[TD]

English​

[/TD]
[TD]

18/04/2017​

[/TD]
[TD]

1200-1400
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Science​

[/TD]
[TD]

English​

[/TD]
[TD]

20/04/2017​

[/TD]
[TD]

1200-1400​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD]

Math​

[/TD]
[TD]

18/04/2017​

[/TD]
[TD]

1200-1400​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD]

Math​

[/TD]
[TD]

20/04/2017​

[/TD]
[TD]

0800-1100​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD]

Math​

[/TD]
[TD]

24/04/2017​

[/TD]
[TD]

1200-1400​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD]

Science​

[/TD]
[TD]

18/04/2017​

[/TD]
[TD]

1200-1400​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD][/TD]
[TD]

Science​

[/TD]
[TD]

20/04/2017​

[/TD]
[TD]

0800-1100
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD][/TD]
[TD]

Science​

[/TD]
[TD]

24/04/2017​

[/TD]
[TD]

1200-1400​

[/TD]
[/TR]
</tbody>[/TABLE]


Then in Data Validation, List, you can use:
Observe the $ on each formula to set absolute references

COURSE (C2)
=OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,1)

DAY (D2)
=OFFSET(Sheet2!$B$1,MATCH(Sheet1!$C2,Sheet2!$B:$B,0)-1,1,COUNTIF(Sheet2!$B:$B,Sheet1!$C2),1)

TIME (E2)
=OFFSET(INDEX(Sheet2!$D$2:$D$100,MATCH(Sheet1!$C2&"|"&Sheet1!$D2,Sheet2!$B$2:$B$100&"|"&Sheet2!$C$2:$C$100,0)),,,COUNTIFS(Sheet2!$B$2:$B$100,Sheet1!$C2,Sheet2!$C$2:$C$100,Sheet1!$D2))

Hope this helps

M.

 

[Marcelo Branco]

oops...
Forgot to say: dates in my table as dd/mm/yyyy

M.