google-sheetsgoogle-sheets-formulaconditional-formatting

Trying to build a recipe/ingredient tracker in Google Sheets with conditional formatting

I'm playing a game with a crafting system in it, but no convenient way to see recipes and inventory, so I built my own. I have an Inventory column listing ingredients and quantities, and then necessary ingredients listed under each recipe title.

I want to highlight the ingredients under each recipe when I have them available based on their values in the inventory list.

I looked into things like index/match and vlookup but I'm lost. My thinking is I need to accomplish three steps (where A=list of ingredients, B=their quantities, D2:G11=recipes):

  1. Test for B>0. { =if(B2>0, [formula that somehow applies step 2], )?? }
  2. If true, find corresponding column A values within the range D2:G11. {?????}
  3. Apply formatting. {put resulting formula from above into conditional formatting rule?}

Orrr am I doing this backwards and I should start with some kind of array formula over the recipes range which then looks for the corresponding inventory entry and then tests it? So like... For a given cell (let's say D6) within the arrayformula range:

  1. Which cell in column A matches the value in D6? (Let's say it's A10.)
  2. Is the value immediately to the right of A10 (B10) >0?
  3. If step 2 = TRUE, then apply formatting to original cell D6.

Is this possible? Is there a better way to set this up?

Example sheet: https://docs.google.com/spreadsheets/d/1S7ph_0hd5ZbE42jI1wwl2tp5P-ydh_4hlFrvV0XRzBI/edit?gid=2100307022#gid=2100307022

I've never really done any coding, so this kind of thinking is very new to me. Thank you for your help!

Solution

  • Here's one formula you can try out, using it as the custom function for conditional formatting:

    =ROWS(FILTER($A$2:$B, $A$2:$A=D1, $B$2:$B>0))>0

    Conditional formatting in sheet.

    In the formula, D1 should be the first cell in your conditional-formatted range.

    FILTER selects the rows of the ingredients list with that name and quantity >0; ROWS computes the number of such rows.

    References