I am attempting to create a dependent drop down list. I have 2 named ranges in the attached file created with this formula.
List1=OFFSET(Sheet1!$A$2,0,0,(COUNTBLANK(Sheet1!$A:$A)+COUNTA(Sheet1!$A:$A)-1),1)
List2=OFFSET(Sheet1!$B$2,0,0,(COUNTBLANK(Sheet1!$B:$B)+COUNTA(Sheet1!$B:$B)-1),1)
I have a drop down list in Cell B4 of Sheet2 consisting of unique values in Column A of Sheet1. I am attempting to create a dependent drop down list of Column B values in Sheet1 in cell E4 of Sheet2. The formula I have is as follows.
=IFERROR(INDEX(List2,SMALL(IF($B$4=List1,ROW(List2)-ROW(Sheet1!$A$2)+1,ROW(1:1))),"")
However, I am not able to create the dependent list. Any suggestions on how to resolve this?