Choose function?

[Vivilc]

Hi all,

I am stuck with something that I need to prepare for work. It is probably easy to fix, but I have not that much experience with Excel yet.. 

My problem: I have a large dataset (table) in tab number 1 (Hunderds of rows and a few coloums with both quantitative and qualitative values). So for example the titles/colums of my table say 'type' and classified the rows in a few specific types as: sold / bought / option etc. 

In tab number 2 I want to have the same list but then only the rows that match with one specific qualitative value, for example 'sold'. So, the result would look the same as if I would copy the table from tab number 1, but then leave a permanently filter on 'sold' for my list in tab number 2. I just don't know how to get that 'permanently'.

I have tried a lot, and I think I should use CHOOSE and than combine this with a condition? 

However, unfortunately I didn't succeed yet. 

Any tips would be much appreciated!

[#2]

Hi there@Vivilc 

I'm not sure I understood your requirements in full but let's hope I did. Please look at attached file. Tab "all data" would be the big list with hunderes of rows

Sheet "filtered" is the one where you filter (for example sold, bought, etc.) You could us the slicer (marked in yellow) to make the selections. Is that what you are looking for?

[#3]

Hi @PascalKTeam,

Thanks for your reply to my vague question ;) 

I forgot to write that I wish to have it 'dynamic'. So, that if I would add a new row in tab 1, it would than be automatically be added or not on tab 2 as well (depending on the filter ofcourse). 

I thought that I had to use a formula as Vlookup or CHOOSE if, but your suggestion with the slicer works well for my problem! 

Cheers!

[#4]

Hi Vivilc,

What you're interested in is the Excel Filter() function:
https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759
This will return your dataset from the other sheet and filter it on the condition programmed in.

You could even get really clever and set the condition to a drop down, created from the column in the other sheet and then you could switch between sold and bought etc..

[edit]

Apologies, new here, and didn't realise the "related" post thrown at me was two years old.