Dependent Drop-Down w/ Dynamic Lists

[covanpatten2]

Hi All,

Hoping you can help - I've been stuck on this for a while and feel as though I've tried just about everything. I've been on google for days viewing tutorials and various forums, but can't seem to figure this out.

I'm looking to put together a dashboard that has dependent drop-down lists (e.g., when a team is selected in B2, individuals can be selected in C2). If an individual is selected in C2, stats show for that individual - but if no individual is selected (blank), then stats for the entire team (selected in B2) are shown. Does that make sense? This can be seen in the "Dashboard" tab.

Stats are pasted into the "Names Paste" tab, and can therefore allow for data to be updated simply by pasting new info into the cells. If someone leaves the team, just paste new data in and the dashboard will be updated.

I therefore want dynamic ranges to reflect what's entered into the "Names Paste" tab. See the "linkage" tab, where I've used an index-match formula to show lists that will grow and shrink based on the info pasted. I've then used a standard offset-counta formula to name these ranges (purposefully including a blank at the beginning of the range and excluding all blanks after the names end). The idea is that this will allow you to select the players in that team via the drop-down cell, without having to manually update the named-ranges every time something changes. Does that make sense?

For some reason, if the named-range is not dynamic, this works without a problem (see, for example, Team "five" - which is not dynamic. They can be selected and viewed correctly in the dashboard. However, if a player was added to that team, the drop-down wouldn't be updated automatically). I'm not sure if it's the offset formula that the dependent drop-down cell doesn't allow or if it's something else.

Does anyone have any insight or advice? Hopefully there's an easy solution, as I can't imagine this is the first time that anyone's asked about such a problem. Let me know if you have any questions!

*PS: This is a very simplified version of what I'm ultimately working with - I have a sheet with 5000+ individuals/teams and 3 or 4 drop-downs. Everything else works great, but it's this final dependent-dynamic drop down I'm struggling with). I've tried to name ranges without using offset (like an index), but haven't had any success.

Thanks so much

[dflak]

Here is my guess at where I think you are going.

First of all, I put the information in an Excel Table because Excel Tables know how many rows and columns they have, so any formulas referencing them don’t have to change when the amount of data in the table changes. Also the formulas reference ranges by row headers which makes them a lot easier to read.

This article should get you started with tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel.

The key to the enterprise is the Helper Page that has pivot tables that are overlaid with named dynamic ranges.

If you need help with named dynamic ranges, look here: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges

The first pivot table is simply a list of the team names. I overlaid this with a name called Team_List. Pivot tables are a very easy way to get a unique list of names. I included the row header in the list and manually changed the title to (All).

Team_List is used for data validation in cell C2 on the Dashboard page.

When Cell C2 is changed, a change event (VB code) copies the selected value into Cell E1 on the Helper Page. Cell E1 is the filter for a second pivot table and it gets a list of people associated with that team. The named range, Name_List is overlaid on the results.

Then we go back to the dashboard: Cells D2 and D3 have formulas that are equal to whatever is selected in Column C or if (All) is selected, then it changes it into an asterisk, (*) which is the “match all” wildcard for formulas that accept wildcards.

Then for the stats, the formula is: =SUMIFS(Table_Team[Stat 1],Table_Team[Team],$D$2,Table_Team[Names],$D$3)

So if all (All) selected in Cell C2, you get a list of all players regardless of team. If you further select (All) for the player’s name, then you get a sum of all of that statistic for everyone.

If you select a specific team in Cell C2, then you get a list of only the players on that team. If you select (All) for the player’s name, then you get a sum of that statistic only for players on that team.

If you select a specific player (whether or not you first select a specific team), you will get the statistics for that player.