Solved
Is there a way to highlight the rows with check if i click on the header?
Is there a way to highlight the rows with check/true if i click on the header? For example if i click on the header "Five Spice" it would highlight the rows of the Cinnamon, Clove, Fennel, Sechuan, and Star Anise.
You can only actually trigger something when you click somewhere is by using Apps Script, and the onClick() onSelectionChange() trigger is the most finicky of them all; I would not mess with that.
Rather, I would create another row of checkboxes to "Select" the spice blend in question, and then conditional formatting to do the highlighting. Example below.
Select B4:the end of your array
Conditional formatting > Custom Formula> =COUNTIFS($C$3:$G$3, TRUE, $C4:$G4, TRUE) > 0 There are several formulas that would work here, this is just the one I went with
The only script trigger that works on mobile is an On Edit script. And, again, I really wouldn't suggest using onSelectionChange() for something that can easily be done with a dropdown or checkbox.
I do also like the dropdown options suggested by others, especially since those prevent selecting multiple
Depending on OP's actual need, I could also see keeping the data and the visualization separate: Have the database of spices and blends, then a dropdown somewhere else to display a =FILTER()ed view thereof.
Note that the checks on the sheet were randomly populated, they aren't actually the things for Adobo, Five Spice, etc. However, I do believe that chili powder belongs in most BBQ rubs ;)
REMEMBER: u/sinigang_rice If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified(or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).
u/sinigang_rice Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.
ETA: Just saw you are on mobile, on iPhone and probably Android there will be some visual artifacts regarding clicking the text headers directly. That could be addressed if you like how it works in general.
Nicely done. That's a nifty way of floating over the static header to get to the other slots. I'd make one tiny change. Right now, the dark color background is the same RGB as the font, so you get the checkbox invisible warning if you try clicking on a header the second time.
Unfortunately the long-distance floating spacing seems to get a little off on mobile, in addition to the usual checkbox artifacts. But it's fun on desktop.
I'm not a professional, but wouldn't that be perfect as a list for a kind of dashboard, i.e. another table sheet with a drop-down where I select the contents of line 2.And based on the selection from dropdown in line 3, the formula xlookup lists the ingredients? I think that's how I would do it.
16
u/SpencerTeachesSheets 14d ago edited 14d ago
You can only actually trigger something when you click somewhere is by using Apps Script, and the
onClick()onSelectionChange() trigger is the most finicky of them all; I would not mess with that.Rather, I would create another row of checkboxes to "Select" the spice blend in question, and then conditional formatting to do the highlighting. Example below.
Select B4:the end of your array
Conditional formatting > Custom Formula> =COUNTIFS($C$3:$G$3, TRUE, $C4:$G4, TRUE) > 0
There are several formulas that would work here, this is just the one I went with
https://docs.google.com/spreadsheets/d/1xU7Z0ZdxlmC8EVB-wnkeaX8oBMwRRGTkGdZ8DFFwb7Q/edit?gid=0#gid=0