Return Values Based on Drop Down in Cell

[ccowman]

Hi everyone,

I'm trying to do a few things and I'll start off with the easy part of the two parts then jump into the second...

The first, I have a ton of data for specific projects listed in one tab, called ProjectData (Requirement #, Release Date, Owner, etc.). What I'm trying to do is have a Dashboard page where the owner of any of the projects can pick their team from the dropdown and in the cells below, the appropriate data from the first tab will be shown. What I have done is basically tricked Excel by saying =IF(ProjectData!ProjectTeamName=Dashboard!ProjectTeamDropDown, ProjectData!A1,"") and copy this formula across all the cells that I would like to return. When the user selects "Project #1", a lot of the cells returned are blank (only the ones with Project #1 show the correct data). This results in having to add Data->Filter to the data and the user selecting to Hide Blanks. Is there any way to accomplish what I'm trying to do without any necessary interaction from the user? Also, if the user selects a Project (Project #2), but they would like to return to seeing all Projects, I'm not sure how to accomplish this? Maybe some use with a Wildcard*?

The second part, I would like to add a few more dropdowns so the user can get deeper into the data by only touching dropdowns. So once Part 1 is accomplished, I'd like to add DropDowns for Release Date (Jan 2014, June 2015, etc.) and also Owner (John Doe, Jane Doe, etc.).

Essentially, a user could drill down to something like this... Project #1-Jan 2014-John Doe and return 3 results vs. the 300 for Project #1.

Hope that helps and if anyone would like to see an example, let me know and I can provide one. Thanks for the help in advance!

[FDibbins]

perhaps it would be easier to help you if you uploaded a sample workbook, showing what data you are working with, what your expected outcome would be, and how you would arrive at that

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[ccowman]

Hi @FDibbins and everyone else,

I have attached an example of the data that I'm working with and what I'm trying to accomplish. What I would like to have happen is what is on the 'Requirements' tab be transferred over to 'XTeam_Matrix' (start in cell B29) based on the selections made in the dropdowns on 'XTeam_Matrix' (XTeam_Matrix!C22:C25). So if the selections made in the dropdowns are "CCA #2", "April 2013", "John Doe", "All", I would receive two results. Another example is "CCA #6", "All", "All", "Frontier" and 8 results would be returned.

The other two tabs "CCA#1" and "CCA#2" are just some other examples of what else I'm going to do. I'm going to have a tab for each Project and will have similar drop-downs to what is on the 'XTeam_Matrix' tab.

Hopefully that helps explains some things and any help that could be provided would be greatly appreciated. Thanks!

_Requirements_Dashboard_Test.xlsx