Dependent Drop down list

1. Dependent Drop down list

Dear All Excel Guru (need help Obi Wan formuola),

I am trying to complete a dependent drop down list - which one excel forum member was very kind to assist with, however i am trying to figure out the following- and not having much luck - running out of time to get this S/S done as well.

The dependent criteria will be fixed string EG "Retired"
The list of data to search upon is not sorted... (see pic)

That being said - I actually want to create a list in the drop down of all course which are not equal "retired"

this is the formula to -date based on eq to "retired" (B2 being retired) , be seems to return the first retired course and all the currents in between as well

=OFFSET('Static Data'!\$J\$1,MATCH(B2,'Static Data'!M:M,0)-1,,LOOKUP(2,1/('Static Data'!\$M\$5:\$M\$40=B2),ROW('Static Data'!\$M\$5:\$M\$40))-MATCH(B2,'Static Data'!M:M,0)+1)

3. Re: Dependent Drop down list

oops -updated with correct file

4. Re: Dependent Drop down list

as i am unsure how to do it in one formula

i used combination of offset formula and a helper column

helper column (CSE formula)
Formula:
`Please Login or Register  to view this content.`

not sure if that countif is required for this particular example as you should not have duplicates? but just in case

Offset formula (for named range)
Formula:
`Please Login or Register  to view this content.`

5. Re: Dependent Drop down list

Great stuff will take a look. I wasnt sure what the countif function provided in terms of listing, But now i understand. THX

6. Re: Dependent Drop down list

the countif in the helper eliminates duplicates should that be a problem for you

the countif in the offset works in combination with counta with counts the whole array length

in the example
COUNTA returns 36
countif returns 33
so your offset array length is 3

if you dont include your DV will be very long with a lot of empty spaces

There are currently 1 users browsing this thread. (0 members and 1 guests)