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 i...
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

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

  • jacparks's avatar
    jacparks
    Copper Contributor
    Jun 28, 2021

    Yea_So Ok, can I pick your brain some more.  

    On the FLOWER sheet, is there a way that if there are blanks in the ingredients list that it skips to the next cell that has a vale when it fills in the J1:AC1?

     

    • 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

      • jacparks's avatar
        jacparks
        Copper Contributor
        Jun 28, 2021
        Do you think the Ingredient list on the left is causing me problems and I can just do the data validation lists right across the top of the page? Would that be any better? That would change all of my other formatting and formulas though.
    • 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

       

       

       

       

      • jacparks's avatar
        jacparks
        Copper Contributor
        Jun 23, 2021
        Hi! For this formula

        and put a formula:

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

        Where it says if statement here, I'm not exactly sure what I'm putting there.

        Something like this? This doesn't work but am I on the right track?
        =IF('Flower Counter'!J6 > 0, ='Flower Counter'!J6, "")
    • Yea_So's avatar
      Yea_So
      Bronze Contributor
      Jun 22, 2021

      Hi jacparks ,

       

      What does the highlighted numbers represent? for example bridal bouquet, says 3 on the first column, does it represent 3x10? or 3 stems?

       

      cheers

      • jacparks's avatar
        jacparks
        Copper Contributor
        Jun 22, 2021
        It’s 3 stems of the alstroemeria in the bridal bouquet.

Resources