Dynamic restrict data in List box

[Anonymous]

Hello,

I have list box with field from table - table containts records with calendar data - from this field is creating menu for years - actually 2007, 2008, 2009 ... 2013.

I can restrict values in list box, because 99% users are working 2012 and 2013.

But I can't restrict all data in script - reload application is more time-expansive and when some user can all years, I have to give it to him immediatelly.

So, I'm searching for possibility restrict values in list box in design, in properties? Ideal: I create some variable, on demand of value this variable I'll show complete year values or only restrict.

Do you know some way?

Thanks,

Olda

[Gysbert_Wassenaar · Accepted solution]

Use an expression for your listbox instead of a field. For example =if(Year>2011,Year) to show only years larger than 2011. Instead of 2011 you could use a variable to make it more dynamic (ie change the value of the variable instead of the expression).

[Anonymous]

Hello,

I tried create expression =Year (it's field from table) and then condition for Show, but List box was full of ##- instead of really values and count of values was same as years 2007-2013.

O.

[Anonymous]

Sorry,

in experiment were in listbox values "2##", not "##-" (there was another try)

[MayilVahanan]

HI

Do u need restriction in list box, use if(Year > 2010, Year) in listbox expression option..

If u need to use in variable and use in many place means, ALT+CTRL+V and add variable vMaxYear and Then give 2010..

Wherever u need, use like this

=if(Year > vMaxYear, Year) or in expression like this sum({<Year = {'>=$(vMaxYear)'}>}Sales)..

Hope it helps

[Anonymous]

Many thanks, I understand it, before I started the new expression and condition for visibility. When I replaced field with expression, all is OK.

Thanks,

O.

[Anonymous]

Hello Gysbert,

I try one more question: when I have shorter lis of values, can I set new width of ListBox depend on condition for values? For example, full List box have 200 px, when I restrict list box only for last 3 year, I can set width 120 px.

Is possible to use some trigger on sheet?

Thanks,

O.