MS Excel Views with Filters

New Contributor

Greetings all and I hope you can help.

Attach is my excel project. The goal is to execute filters on the Source tab and have the target tab reflect the results (same format) as the Source's filtered results.

 

For example:

1. Open attached Excel spreadsheet

2. On the "Source" datasheet is a display with filters

3. Locate and select "Instruction Name" column's drop-down box and select only "MAN 2101-01 Acceptance"

4. Locate and select "Applicability" column's drop-down box and select only "QAS"

5. The result displays 7 (seven) rows

 

The object is for the "Target" tab to reflect the exact display as the filtered "Source" tab.

Subsequently, future "Target" tabs will be added with different filtered results.

Help!

 

Software: MS Excel 2016

 

v/r

Redace2008

7 Replies

@Redace2008 

- add helper column to Source with

=AGGREGATE(3,5,B2)

- add headers to Target Sheet

- in A2

=IFERROR(
   INDEX(Source!A$2:INDEX(Source!A:A, COUNTA(Source!$B:$B)+1),
         AGGREGATE(15,6,1/(Source!$G$2:INDEX(Source!$G:$G, COUNTA(Source!$B:$B)+1)=1)*
                   ROW(Source!A$1:INDEX(Source!$B:$B, COUNTA(Source!$B:$B)+1)),
                   ROW()-ROW($B$1))),
"")

- drag to the right till next column

- drag entire line down till empty cells appear (with some gap)

 

@Redace2008 

 

What you're looking for is very simple with the most recent version of Excel. The solution I'm attaching takes advantage of the recently announced and delivered functions UNIQUE and FILTER.   I'm pretty sure it won't work on Excel 2016, however. So here's a screen grab of the result you could have by upgrading. You'll see the main FILTER function in the function bar at the top... to get different results, you just change the entries in the yellow cells via drop-down selections. The filtered results will change accordingly. For what it's worth, that FILTER function appears in only one cell, E6. All of the results "Spill:" into adjacent rows and columns.

mathetes_0-1612300346688.png

 

@mathetes 

That's 2016

Sergei -- are you saying that Excel 2016 IS the most recent? I do see my version is Version 16.47 of Excel for Mac, but didn't equate that with the year 2016. Interesting. THANKS for updating me.

PC MS Office - Excel ver 16.0

 

Thank you. 

It works. Especially without adding third party solutions. Great answer; however, is writing a script the only way to achieve the same outcome...just asking and still I'm very appreciative and will apply what you have provided.

 

Thanks for your time and effort

 

Redace2008

 

 

@Redace2008 , you are welcome