Dynamic filters using multiple criteria

**dcr8r4life** · 07-10-2015, 02:08 AM

I am looking for some advanced level dynamic filtering advice.

I have successfully applied a dynamic filter to my data and on another tab using an index function of the main data, I am able to only show the data relevant based on 1 drop down list criteria (I.e. Department number).

I would like to be able to choose the columns that I index based on additional drop down list criteria. Is this possible? For example my main data has values in an actuals, business plan, and June update column. On my secondary tab, I am currently just indexing for a selected department number, but I would like to choose either business plan or June update as well and just show the relevant values based on my selection. Is this possible using arrays/sub arrays? I would prefer not to write a macro if possible.

Thanks in advance!

Sent from my iPhone using Tapatalk

**FDibbins** · 07-10-2015, 10:31 AM

Hi, welcome to the forum

Yes, this sounds possible, but to offer better help, I suggest that you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

**aas72** · 07-10-2015, 10:36 AM

Below is an picture of my spreadsheet so you can more easily visualize what I am working with.

7-10-2015 10-30-24 AM.jpg

The picture is of the secondary tab and the headers are a replica of the main data tab. However, on the secondary tab I would like to only show BP YTD or JU YTD based on the drop down menu in cell C3. Is there a way to only select certain coulmns of data from the main data tab when using dynamic filters and the INDEX/SMALL functions?

The Department drop dwon menu controls which department's data shows up below. Ex. if i change department to "110053", then that will replace what is shown in my picture.

I added 3 helper columns to my main data tab and the formula I am using on the secondary tab is =IFERROR(INDEX(Sheet1!$G$20:$M$1077,Sheet1!$P20,COLUMNS($C$5:C5)),"")