I have the following scenario:
One diagram (bar_chart) I have is governed by several variables, but only one is relevant for this:
- Show_per_day (a variable with the values 1_for_daily_display and -1_for_monthly_display.
- In case display is by day, I want only the days of the current week (Mo-Sat) to be displayed - all six of them from Monday. (currently a selection)
- In case display is per month, selecting the current week makes no sense, so only the current month should be selected.
I now have two bookmarks, one selecting only the current month, the other one selecting BOTH the current month AND the current week - but bookmarks don't work quite as they should here: I can use a bookmark as a sheet-level-trigger (on opening), but I cannot put a bookmark on a button (actually I would need two, depending on the current state as it is a switch-button, but that doesn't work.
So I have now used individual selections - this also has the advantage that, when I have two selections (month and week) as sheet-level-triggers, the statusbox shows "July" instead of the formula.
So far, however, I have it as a one-way-street: I know that on opening the sheet, a sheet-level-trigger will select BOTH month and week and display is by day, so the variable has the value 1. When clicking the button to have the display by month (variable multiplied with -1), the selection on the week_field is undone.
When clicking it again, however, nothing changes - the month remains selected, not the week. Ideally, however, I would like the selection on the week_field to be redone when switching the view to daily again.
Can anyone give me a hint as to how to do this?
One idea I had was to use an "indirect selection": On a week_listbox, I can write a formula rgd. the current month and all four weeks will be pre-selected (which is equivalent to de-selecting as the month remains selected)- but that does not seem to work as a trigger - if I could do that, I could use a formula for two alternative selections on the week_field:
- the current week or
- all weeks of the current month (which would be for monthly display)
Thanks a lot!