Hey, if i may please get some assistance, my main intention with the table below as a sample.
1). Under "Beverage" (cell J2) have individual check boxes to filter out individual or multiple criteria at once.
2). Instead of having individual check boxes for each Type of drink (coffee, soda, water, ect.), is it possible to just have "Coffee" (cell L2) as a header and just using 2 checkboxes for Yes or No. If "Yes" is checked, it will show both Coffee and all other type of beverages. If "No" is checked, it will only show every other type of beverages excluding coffee.
3).Under "Prev. day sales" (cell N2) can excel assign different category with number range with checkboxes so that if i check one or multiple boxes, it will only show what is checked within those specific range?
The 4 category would be named in the cell as-
0-10 (so only show day sales 9 or under)
10-20 (only show day sales 10-20)
20-30
30+(show day sales 30 or more)
4). Instead of doing specific full dates, can a certain year be checked instead, as well as specific or multiple months. For example, only want to show 2024 AND 2023 with only Jan, Feb, Mar checked.
I understand these are loaded questions. I have done some research on my behalf and only found a conclusion for question 1 with the filter function method I believe. But before I decide to make that change I was hoping the following questions is also doable for it to be worth it. If not I would have to scrap this idea up and move onto other methods instead unfortunately (no checkbox).
Edit: I uploaded a image to show where the checkboxes are in the cells. Xl2bb for whatever reason does not capture the checkboxes.
1). Under "Beverage" (cell J2) have individual check boxes to filter out individual or multiple criteria at once.
2). Instead of having individual check boxes for each Type of drink (coffee, soda, water, ect.), is it possible to just have "Coffee" (cell L2) as a header and just using 2 checkboxes for Yes or No. If "Yes" is checked, it will show both Coffee and all other type of beverages. If "No" is checked, it will only show every other type of beverages excluding coffee.
3).Under "Prev. day sales" (cell N2) can excel assign different category with number range with checkboxes so that if i check one or multiple boxes, it will only show what is checked within those specific range?
The 4 category would be named in the cell as-
0-10 (so only show day sales 9 or under)
10-20 (only show day sales 10-20)
20-30
30+(show day sales 30 or more)
4). Instead of doing specific full dates, can a certain year be checked instead, as well as specific or multiple months. For example, only want to show 2024 AND 2023 with only Jan, Feb, Mar checked.
| Sample.xlsx | |||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | |||
| 1 | |||||||||||||||||||||||||||
| 2 | Date | Beverage | Type | Inventory | Prev.day sales | Morn. sales <10:00 | Mid sales 11-14:00 | After. sales 14:00> | Beverage | Coffee | Prev. day sales | Year | Month | ||||||||||||||
| 3 | 01-Jan-24 | Latte | Coffee | 50 | 37 | 20 | 14 | 1 | 7 Up | Yes | 0-10 | 2020 | Jan | Apr | Jul | Oct | |||||||||||
| 4 | 01-Mar-24 | Coke | Soda | 50 | 32 | 3 | 15 | 6 | Cappucino | No | 10-20 | 2021 | Feb | May | Aug | Nov | |||||||||||
| 5 | 06-Apr-21 | Mount Franklin | Water | 50 | 25 | 20 | 16 | 5 | Coke | 20-30 | 2022 | Mar | Jun | Sep | Dec | ||||||||||||
| 6 | 04-Mar-20 | Espresso | Coffee | 30 | 15 | 7 | 4 | Espresso | 30> | 2023 | |||||||||||||||||
| 7 | 12-Feb-20 | Cappucino | Coffee | 30 | 26 | 10 | 7 | Fanta | 2024 | ||||||||||||||||||
| 8 | 26-Jul-23 | Pepsi | Soda | 40 | 11 | 1 | 7 | 2 | Getarade | ||||||||||||||||||
| 9 | 27-Sep-21 | Mountain Dew | Soda | 20 | 7 | 3 | 1 | Latte | |||||||||||||||||||
| 10 | 17-Sep-21 | 7 Up | Soda | 20 | 7 | 4 | Mount Franklin | ||||||||||||||||||||
| 11 | 22-Nov-23 | Red Bull | Energy | 30 | 10 | 5 | 5 | Mountain Dew | |||||||||||||||||||
| 12 | 05-Oct-23 | Getarade | Energy | 30 | 5 | 1 | 9 | Pepsi | |||||||||||||||||||
| 13 | 13-Sep-21 | Fanta | Soda | 20 | 7 | 6 | 2 | Red Bull | |||||||||||||||||||
| 14 | |||||||||||||||||||||||||||
| 15 | |||||||||||||||||||||||||||
Sheet1 | |||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J3:J13 | J3 | =SORT(UNIQUE(Menu[Beverage])) |
| Dynamic array formulas. | ||
I understand these are loaded questions. I have done some research on my behalf and only found a conclusion for question 1 with the filter function method I believe. But before I decide to make that change I was hoping the following questions is also doable for it to be worth it. If not I would have to scrap this idea up and move onto other methods instead unfortunately (no checkbox).
Edit: I uploaded a image to show where the checkboxes are in the cells. Xl2bb for whatever reason does not capture the checkboxes.
