I have a corporate parent and under each corporate parent i have 3 to 4 communities. I have a calculated column which has numbers from 0 to 23 representing the hours of the day. My requirement is to create numeric cascading filter action dropdowns on the sheet. I want to give my user the flexibility to choose the time from the three dropdowns. There are total three shifts for the workers, but not every community has same shift timing so, the flexibility should be, the first dropdown should have all the numbers 0 to 23, the user can select anything. But for the second dropdown, when the user selects 5 in the first dropdown then, the values in the second dropdown should start from 7 and show all the values up to 23. Likewise if the user selects 9 in the second dropdown, then the values in the third dropdown should start from 10 to 23. I want this dropdowns to be dynamic, as the shift timings are not fixed or particular like 8 or 9 hour shifts.
Hi,
One way I can think of is to create a dataset with the hierarchy needed for the parameters, use cascading filters / relevant values and use those to filter your data. The dataset would look something like this.
| 0 | 1 | 2 |
|---|---|---|
| 0 | 1 | 3 |
| 0 | 1 | 4 |
| … | … | … |
| 0 | 1 | 21 |
| … | … | … |
| 0 | 1 | 22 |
| 0 | 1 | 23 |
| 0 | 2 | 3 |
| 0 | 2 | 4 |
| 0 | 2 | 5 |
| … | … | … |
| 20 | 21 | 22 |
| 20 | 21 | 23 |
| 21 | 22 | 23 |
Hope this helps!