Dependent Lists

**chrisucla12** · 02-02-2013, 05:56 AM

Source Data under "Nerves_Data" tab:

Want to create a dependent list under "Day of Week Analysis" to see what sport and what corresponding days have the most impact (lift) 5 weeks up to game day, and 5 weeks after.

Need:

1) Formula to create a list (Day of Week) dependent on first list (Game Type)... see "Day of Week Analysis.. " Would want to have unique data from Nerve_Data!$A$2:$A$25 selected, no duplicates within the list.

2) Use both lists as a filter to return the 5 weeks up to game day and 5 weeks after data to display whenever data from each list are selected.

Thoughts?

**JBeaucaire** · 02-02-2013, 04:06 PM

1) create a single named named range called Game_Type which is defined as =Nerves_Data!$A:$A

2) Apply Data Validation settings to the Day of Week Analysis sheet cell B20 as:

Allow: List
Source: =OFFSET(INDEX(Game_Type,MATCH($B$19, Game_Type, 0)), , 1, COUNTIF(Game_Type, $B$19), )

That should do it.

**JBeaucaire** · 02-02-2013, 04:07 PM

1) create a single named named range called Game_Type which is defined as =Nerves_Data!$A:$A

2) Apply Data Validation settings to the Day of Week Analysis sheet cell B20 as:

Allow: List
Source: =OFFSET(INDEX(Game_Type,MATCH($B$19, Game_Type, 0)), , 1, COUNTIF(Game_Type, $B$19), )

That should do it.

**chrisucla12** · 02-02-2013, 04:40 PM

Thanks Jerry -

The more I look at the data the more it seems that a better depiction of the data would utilize unique values of Day of Week as a List, then return the Game Type with the corresponding 5 Weeks Out to 5 Weeks After Data. (That way I can graph the day of week impact on particular game types).

Please let me know if it this is best suited as another thread... thought this was a valid follow up to the dependent list question above. Again, thank you much in advance.

Chris.

**JBeaucaire** · 02-02-2013, 05:51 PM

Your original data and the solution offered go together and appear to work correctly. Be wary of adding new designs that toss out working stuff. But yes, if this question is resolved, mark this thread as solved and present new scenarios/needs in new threads.