how to exclude a value from list box

[venkat1]

hi 

my list box expression was like this in the list box 

=IF(YM.YEAR_1=2016,'2015-16',
IF(YM.YEAR_1=2017,'2016-17',
IF(YM.YEAR_1=2018,'2017-18',
IF( YM.YEAR_1=2019,'2018-19',
IF(YM.YEAR_1=2020,'2019-20',
IF(YM.YEAR_1=2021,'2020-21',
IF(YM.YEAR_1=2022,'2021-22',

)
)
)
)
)
)
)

now i dont want to the year YM.YEAR_1=2016 , how to write an expression to not show YM.YEAR_1=2016 in the list box 

[vikasmahajan]

try some thing like this =IF( not match( YM.YEAR_1,'2016'),0 , IF(YM.YEAR_1=2017,'2016-17', IF(YM.YEAR_1=2018,'2017-18', IF( YM.YEAR_1=2019,'2018-19', IF(YM.YEAR_1=2020,'2019-20', IF(YM.YEAR_1=2021,'2020-21', IF(YM.YEAR_1=2022,'2021-22', ) ) ) ) ) ) )

[sunny_talwar · Accepted solution]

How about you just remove it from the if statement

=If(YM.YEAR_1 = 2017, '2016-17',
If(YM.YEAR_1 = 2018, '2017-18',
If( YM.YEAR_1 = 2019, '2018-19',
If(YM.YEAR_1 = 2020, '2019-20',
If(YM.YEAR_1 = 2021, '2020-21',
If(YM.YEAR_1 = 2022, '2021-22',
)
)
)
)
)
)

[venkat1]

hi it was working if write like this 

=IF( not match( YM.YEAR_1,'2016'),
IF(YM.YEAR_1=2017,'2016-17',
IF(YM.YEAR_1=2018,'2017-18',
IF(YM.YEAR_1=2019,'2018-19',
IF(YM.YEAR_1=2020,'2019-20',
IF(YM.YEAR_1=2021,'2020-21',
IF(YM.YEAR_1=2022,'2021-22',

)
)
)
)
)
)
)

i have another question for suppose if i want to hide 18 and 19 years, then how i tried this way it was showing error in expression.

[andradetobias]

You can exclude this in your load.
If you want to keep this year on your load, you can use the expression bellow

if(not match(FIELDNAME,'CRITERIA'),FIELDNAME)

[venkat1]

hi sunny 

i need a help from you regarding a scenario.

i am having year and month list boxes and pivot table with sales of gold and diamond by branch wises,

my requirement is to show by default max(date) sales ,where date fields are different for the gold and diamond,eg. g.vou_date,d.vou_date, now if i select the year and month list box then sales should come accordingly.

i tried with open sheet triggers of two field events with d.vou_date and g.vou_date but it was not working. 

can you please help me how to do this scenario.

[sunny_talwar]

I am not sure I understand? How is this related to excluding a value from list box? Is this a new requirement? or is this somehow linked?

[venkat1]

this was a new requirement.

[sunny_talwar]

If this is something new, I would suggest to open a new thread for it. For this thread, did you find what you were looking for? If you did, please close this thread.

Best,
Sunny