Is it possible to exclude values from a listbox?

[Anonymous]

I have a tabel with sales representatives.

In the tabel are sales representatives who still work with the company and sales representatives who doesn't work at the company anymore.

I don't want to delete the sales representatives who doesn't work at the company anymore, because I want to be able to see how much they sold in the past, but I don't want to see them in my listbox.

Is there a formula I can use to exclude the sales representatives who doesn't work at the company anymore from the listbox?

Now I am using a formula that shows the sales representatives who still work with the company but everytime there is a new sales representatives I have to add him/her to the formula.

AGGR(ONLY({<[Sales Rep Description]={'Algemeen',Edvaldo Rodrigues','Elke Geens','Emilia Canadas (Bibav)','Joaquim Casajuana','Jeroen Bennink','Jody van der Plas','Jos van Nieuwstadt','Roberto Kik','Roland van Meggelen','Ronald Tuyl','Sales Internationaal',Vertegenwoordiger onbekend'}>} [Sales Rep Description]),[Sales Rep Description])

I tried this: if([Sales Rep Description]<>'Alexander Decock',[Sales Rep Description])

And it works but only with 1 value.

If you use it with 2 values in stead of excluding them form the listbox, the listbox only shows this 2 values.

if([Sales Rep Description]<>'Alexander Decock','Arie Kegel',[Sales Rep Description])

[Gysbert_Wassenaar · Accepted solution]

The easiest solution would be to add a field in your data model that lets you keep track of who's left and who's current. You can then use another listbox with that field to select only the active sales representatives.

Alternatively you can try something like:

=If(NOT match([Sales Rep Description], ....comma separated list of values here.... ), [Sales Rep Description)

[Anonymous]

That will indeed be the easiest way but there are also sales representatives who doesn't work at the company anymore since last month for example and we want to see what the turnover is for the last 2 seasons.

The formula is working, there is only 1 value I can not add to the formula and that is 'Royalty BV's'.

I think it's not working because of the BV's so I tried BV"s and BV''sbut that's also not working.

Do you perhaps have a solution for that too?

[Gysbert_Wassenaar]

You could work with dates of resignation too.

Double single quotes works here: 'Royalty BV''s'

[Anonymous]

Thanks!