So I have tried to make the attempt myself with whatever research info I have found online so far. Unfortunately I cant get anywhere near what I am after.
I am aware this is a loaded question and probably does not make much sense. I do appreciate any assistance If this is possible, and thank you in advance.
So in this example, the new filtered down table would show, all the Beverages from (J2-J5), which includes Beverages that are "Coffee" from (K2). If (K2) is blank, return value should only show Beverages from J2-J5 that IS NOT a Coffee (only Coke & Mountain Dew would be visible in this example). If any of J2-J5 cell are empty, exclude those values from the table. So if (J3) is empty, results will only come back for J2, J4, and J5. If ALL J2-J5 are empty, results will return with "No specific data" in the new table and no data should be visible for this scenario.
Now between what is visible so far, filter it down even further and show current Beverages that is less than (L2) and greater than (L3). If (L2) is blank, than return value will only show current Beverages greater than (L3). If both L2-L3 are blank, return table will only show "No specific data" with no data shown. (typically, there will always be at least 1 value between those two cells)
Same goes for years and months, filter down to 2020 and 2024 in Jan and Aug. But If both cells are blank either from M2-M3 or N2-N3, return table to "No specific data". (Again, typically there will always be at least 1 value between (N2-N3) AND 1 value from (M2-M3), Meaning, there will always be at least ONE year and ONE month present in the assigned cells.
If there are no data that matches all specific criteria, return as "No specific data"
Going by what is shown in this sample sheet, the final table should only show Latte and Mountain Dew values.
Note: (K2) will be the only cell that can be blank. Which means if (K2) is blank, return should be all values from the other columns but EXcludes only "Coffee" type drinks. In all other cases there will ALWAYS be at least one value from the other columns (J,L,M,N). If only 1 column is fully empty from either J,L,M or N, no data should be shown at all.
I am aware this is a loaded question and probably does not make much sense. I do appreciate any assistance If this is possible, and thank you in advance.
| Sample.xlsx | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 1 | Date | Bev | Type | Inv | PDS | MS | MidS | AS | Beverage | Type | Prev. day sales | Month | Year | ||||
| 2 | 01-Jan-24 | Latte | Coffee | 50 | 37 | 20 | 14 | 1 | Latte | Coffee | 10 | Jan | 2020 | ||||
| 3 | 01-Nov-24 | Coke | Soda | 50 | 32 | 3 | 15 | 6 | Coke | 30 | Aug | 2024 | |||||
| 4 | 06-Apr-21 | Mount Franklin | Water | 50 | 25 | 20 | 16 | 5 | Espresso | ||||||||
| 5 | 04-Mar-20 | Espresso | Coffee | 30 | 15 | 7 | 4 | Mountain Dew | |||||||||
| 6 | 12-Feb-20 | Cappucino | Coffee | 30 | 26 | 10 | 7 | ||||||||||
| 7 | 26-Jul-23 | Pepsi | Soda | 40 | 11 | 1 | 7 | 2 | |||||||||
| 8 | 27-Aug-20 | Mountain Dew | Soda | 20 | 7 | 3 | 1 | Date | Bev | Type | PDS | ||||||
| 9 | 17-Sep-21 | 7 Up | Soda | 20 | 7 | 4 | 1-Jan-24 | Latte | Coffee | 37 | |||||||
| 10 | 22-Nov-23 | Red Bull | Energy | 30 | 10 | 5 | 5 | 1-Nov-24 | Coke | Soda | 32 | ||||||
| 11 | 05-Oct-23 | Getarade | Energy | 30 | 5 | 1 | 9 | 4-Mar-20 | Espresso | Coffee | 15 | ||||||
| 12 | |||||||||||||||||
Sheet2 | |||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J9:M11 | J9 | =FILTER(FILTER(Menu2,(Menu2[Bev]=J2)+(Menu2[Bev]=J3)+(Menu2[Bev]=J4)+(Menu2[Bev]=J5)*(Menu2[Type]=K2)*(Menu2[PDS]<=L2)*(Menu2[PDS]>=30),""),COUNTIFS(J8:M8,Menu2[#Headers])) |
| Dynamic array formulas. | ||
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| _FilterDatabase | =Sheet2!$J$3:$N$10 | J9 |
So in this example, the new filtered down table would show, all the Beverages from (J2-J5), which includes Beverages that are "Coffee" from (K2). If (K2) is blank, return value should only show Beverages from J2-J5 that IS NOT a Coffee (only Coke & Mountain Dew would be visible in this example). If any of J2-J5 cell are empty, exclude those values from the table. So if (J3) is empty, results will only come back for J2, J4, and J5. If ALL J2-J5 are empty, results will return with "No specific data" in the new table and no data should be visible for this scenario.
Now between what is visible so far, filter it down even further and show current Beverages that is less than (L2) and greater than (L3). If (L2) is blank, than return value will only show current Beverages greater than (L3). If both L2-L3 are blank, return table will only show "No specific data" with no data shown. (typically, there will always be at least 1 value between those two cells)
Same goes for years and months, filter down to 2020 and 2024 in Jan and Aug. But If both cells are blank either from M2-M3 or N2-N3, return table to "No specific data". (Again, typically there will always be at least 1 value between (N2-N3) AND 1 value from (M2-M3), Meaning, there will always be at least ONE year and ONE month present in the assigned cells.
If there are no data that matches all specific criteria, return as "No specific data"
Going by what is shown in this sample sheet, the final table should only show Latte and Mountain Dew values.
Note: (K2) will be the only cell that can be blank. Which means if (K2) is blank, return should be all values from the other columns but EXcludes only "Coffee" type drinks. In all other cases there will ALWAYS be at least one value from the other columns (J,L,M,N). If only 1 column is fully empty from either J,L,M or N, no data should be shown at all.
Thank you kindly
