Forum Discussion

jacparks's avatar
jacparks
Copper Contributor
Jun 21, 2021
Solved

Excel - Fill in values from column and row based on specific criteria to make list

Hi! 

 

I am a florist and I make a spreadsheet with the item that I am building (Ex. Bouquet), I specify the flowers I am using at the top then I fill to the right with the number of stems I want in each item (bouquet).  

 

I would like this to then transfer to a recipe sheet where I have a recipe box for each Item (bouquet) and the recipe to specify the flower and how many stems of that flower for that specific Item (bouquet).  

Here is what I have laid out for the recipe sheet.  I cant figure out how to get the data from the table to the recipe card with only the flowers and quantities that are in that specific item. 

I hope this makes sense.  Thanks for your help! I have attached the file. 

 

excel
  • Yea_So's avatar
    Yea_So
    Jun 23, 2021

    jacparks 

     

    Here's a video to make your drop down list a searchable one, you type the first few characters then click on the drop down and will narrow your selection to the appropriate selections:

    https://youtu.be/Ea_ACp5W8zI

20 Replies

  • Yea_So's avatar
    Yea_So
    Bronze Contributor
    Jun 23, 2021

    jacparks 

     

    Here's a video to make your drop down list a searchable one, you type the first few characters then click on the drop down and will narrow your selection to the appropriate selections:

    https://youtu.be/Ea_ACp5W8zI

  • Yea_So's avatar
    Yea_So
    Bronze Contributor
    Jun 23, 2021

    jacparks 

     

    By the way...

    In case you're wondering about the purple highlight on the recipe card box, I used conditional formatting:

     

    that way when there are entries under the other ingredient headings the formatting will follow to which ever row the heading will be at

    cheers

  • Yea_So's avatar
    Yea_So
    Bronze Contributor
    Jun 21, 2021

    Hi jacparks ,

     

    If you share your spreadsheet these techie types will flock around here to flex their brains around it so fill it in with as much data samples as you can and post it on here because they like a good challenge puzzle to solve

     

    cheers

      • Yea_So's avatar
        Yea_So
        Bronze Contributor
        Jun 28, 2021

        Hi jacparks,

         

        We can get creative.  I was thinking that we can include the Ingredient Headers as part of the drop down selection, and once they're selected we can have excel do a conditional formatting to the headers then it'll only skip 1 column at each indgredient header.  What are your thoughts about it.

         

        cheers

      • Yea_So's avatar
        Yea_So
        Bronze Contributor
        Jun 22, 2021

        Hi jacparks,

         

        I added some named ranges:

         

        Then used this formula in the card from this range using:

        =FILTER(Ingredients,NOT(ISBLANK(Ingredients)))

        In

        and put a formula:

        =IF(OR($B5="Focal",$B5="Filler",$B5="Greenery",$B5="Line",$B5="Hardgoods / Rentals",ISBLANK(B5)),"","Insert IF STATEMENT HERE")

         

        Comments:

        In the quantity column is not a good practice to have duplicate values. for example:

        Alstroemeria-assorted 10
        Alstroemeria-assorted 10
        Amaranthus-upright bronze, grn & rd BG

         

        There are two Alstroemeria entries (vlookup or index match cannot be used)

         

         

        If you have any questions, please do not hesitate to reach out.

         

        Cheers

         

         

         

         

Resources