Advanced Filter

[jlo33]

Hello all,

I'm trying to create an advanced filter. I've attached a sample of the data, what I have figured out so far, but also what I'm hoping for the end result.

My gap is what I have figured out vs the end result. I don't need all columns to be transferred over, and I don't know the best way to handle it.

Also, is there a way that when the filtered cells (Figured Out tab - L3:M4) are updated, the table itself is updated without having to redo the advanced filter settings?

Any help would be appreciated.

[CK76]

Start your advanced filter process from your End Result sheet.

That will allow you to select export range as End Result.
List Range would be... "'Source Data'!$A$1:$G$6"
Criteria range "'Filtered Data - End Result'!$E$2:$F$3"
Copy to: "'Filtered Data - End Result'!$A$1:$C$1"

As long as you have exact match for header records you want (Name, Temp Address, Date) in A1:C1 range. It will pull the info meeting your criteria.

Once done. Excel will created 2 named ranges. 'Filtered Data - End Result'!Criteria and 'Filtered Data - End Result'!Extract.
You can use these for future operation.

As for second question, you can use small macro to do it.
Ex:

Please Login or Register  to view this content.

[CARACALLA]

Filtered Data - End Result

A2=IF(CHOOSECOLS(FILTER('Source Data'!A2:G6,('Source Data'!A2:A6='Filtered Data - End Result'!E3)*('Source Data'!E2:E6='Filtered Data - End Result'!F3)),3,6,7)=0,"",CHOOSECOLS(FILTER('Source Data'!A2:G6,('Source Data'!A2:A6='Filtered Data - End Result'!E3)*('Source Data'!E2:E6='Filtered Data - End Result'!F3)),3,6,7))

[jlo33]

@CK76 Thank you. This was super helpful!!

[jlo33]

@CARACALLA

Would you be able to explain this formula to me? I'm trying to figure out where I would place it and which problem it would solve.