Selecting all months to date when activating a worksheet

[pauldamen]

Hi all,

This is probably a question with an easy answer but I can't get it to work. When I activate a worksheet all months to date should be selected in the month listbox (so today that would only be January, tomorrow January and February).

I tried everything with the triggers but I can only manage to select 1 item in the listbox (f.e I also have a year listbox where it should select current year, I solved that one with: =Year(Today('YYYY')), this gives me the current year).

But I can't get it to work to select multiple months. Can someone help?

Regards,

Paul

[swuehl · Accepted solution]

I assume you already have created a trigger for OnActivateSheet in Sheet Properties, with an action Select - Select in Field, with field name

Month

and then you can use as search expression

='('& concat(distinct {<Year= {$(=year(today()))}, Month = {"<=$(=num(month(today())))"}>} Month,'|')&')'

This will create a search string like (for example tomorrow):

(Jan|Feb)

Hope this helps,

Stefan

edit: You probably don't need the set element for Year, so

='('& concat(distinct {< Month = {"<=$(=num(month(today())))"}>} Month,'|')&')'

should be enough

[pauldamen]

Thanks a lot works perfect!