In Google Sheets, I'm making a 'workbook' that keeps track of inventory in a shop. There are 3 sheets in the workbook.
The first one is the raw data which is 'Item Name', 'Initial Stock', 'Stock Used', and 'Stock Remaining'. It gets updated daily.
In the second sheet, I made one summarised table of the data in Sheet 1 using the QUERY function to show only 'Item Name' and 'Stock Remaining' of all the items. Using FILTER functions, I made two smaller tables from that QUERY table to show the items which are finished (0 Stock Remaining) and items low in stock (1-3 Stock Remaining).
Third Sheet is supposed to be a 'Dashboard' of sorts, where I have one bar chart. This is where my problem comes in. I'm trying to use checkboxes to 'toggle' through the different datasets (tables in Sheet 2). For example, I'd have one checkbox intended to show the 'low stock' items on the bar chart when TRUE. So when the user toggles it to TRUE, the bar chart would change it's data to the table in Sheet 2 for low stock. I am not sure where to start exactly and I haven't found a single example of it online so far except for in excel; although when I try using the excel tutorials it obviously doesn't work.
Anyone experienced enough to know if this is feasible?
Thanks 🙂
Best Answer
Figured it out finally if anyone is interested.
The trick I guess is to use Nested IF functions. I ended up using the 'filter functions' with IF statements with the main table. It went something like this:
=IF(lowstock checkbox cell=TRUE, FILTER(cell range for table, conditions of low stock),IF(nostock checkbox=TRUE, FILTER(same cell range for table, conditions of 0 stock)))
The only downfall that I've encountered with a design like this, is that because of the use of IF statements, it doesn't allow to have both check boxes enabled at the same time. What I mean by that is that it will prioritise the first condition over the other if they are both ticked; in this case it prioritises the low stock checkbox conditions since they are the outer function.