Return Excel List with multiple drop down lists

[Randallh]

I hope I can explain this well enough. I have a table that is columns A-P and with duplicate and unique values. The Data I want to match is in column I and column P. One is a name and the other is a date that has been extracted to month and year. I have one drop down list that has names and another that has month and another that has year. I want to return multiple different columns to create a variable list that is based on the points chosen from the drop down lists that will show the data from the table with all data that matches the name and month/year combo. The table is constantly growing and will continue to have multiple months in it for years to come as they get added. Below is a simplified version of my excel.

Data
[TABLE="width: 500"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Report #1[/TD]
[TD]Cost #1[/TD]
[TD]Name #1[/TD]
[TD]Project #1[/TD]
[TD]Date #1[/TD]
[/TR]
[TR]
[TD]Report #2[/TD]
[TD]Cost #2[/TD]
[TD]Name #1[/TD]
[TD]Project #2[/TD]
[TD]Date #1[/TD]
[/TR]
[TR]
[TD]Report #3[/TD]
[TD]Cost #3[/TD]
[TD]Name #2[/TD]
[TD]Project #3[/TD]
[TD]Date #1[/TD]
[/TR]
[TR]
[TD]Report #4[/TD]
[TD]Cost #4[/TD]
[TD]Name #1[/TD]
[TD]Project #4[/TD]
[TD]Date #2[/TD]
[/TR]
[TR]
[TD]Report #5[/TD]
[TD]Cost #5[/TD]
[TD]Name #2[/TD]
[TD]Project #5[/TD]
[TD]Date #2[/TD]
[/TR]
[TR]
[TD]Report #6[/TD]
[TD]Cost #6[/TD]
[TD]Name #1[/TD]
[TD]Project #6[/TD]
[TD]Date #2[/TD]
[/TR]
[TR]
[TD]Report #7[/TD]
[TD]Cost #7[/TD]
[TD]Name #2[/TD]
[TD]Project #7[/TD]
[TD]Date #1[/TD]
[/TR]
</tbody>[/TABLE]
Drop down lists [TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]List of Names[/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]List of months[/TD]
[/TR]
[TR]
[TD]Year[/TD]
[TD]List of years[/TD]
[/TR]
</tbody>[/TABLE]

List of data will return below drop down lists.




I know I can use index formulas, but I just can't seem to figure it out after searching for the answer. If there are other ideas, then I am more than open to them. I have done some VBA coding as well, but I would like to stay away from coding if possible so everybody else that uses this sheet doesn't have to deal with macros.

Thanks.

 

[Rijnsent]

Hi Randallh,
if the whole purpose is to basically output a filtered list, why not make your data into a Table (Excel menu: insert->table), selecting that table and inserting a couple of Slicers (instead of your dropdowns). For the slicer: select the table and add one in the menu Design (for table). That's rather user friendly, e.g. allows for multiple selections.
Hope that helps you,
Koen

 

[Randallh]

Hi Randallh,
if the whole purpose is to basically output a filtered list, why not make your data into a Table (Excel menu: insert->table), selecting that table and inserting a couple of Slicers (instead of your dropdowns). For the slicer: select the table and add one in the menu Design (for table). That's rather user friendly, e.g. allows for multiple selections.
Hope that helps you,
Koen

That works for some of the data, however, there is other data that I would like to capture using calculations between different columns so I can analyze the data a bit more heavily than just filtering and showing the list. Any additional thoughts on help with this? I did try the slicer and it works great for the simplistic filtering and doing it quick which I like and will probably use for the users, but I have my own analytics that not everybody will see so it can be more complex if need be.