Forum Discussion

jacparks's avatar
jacparks
Copper Contributor
Jun 20, 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 23, 2021

Hi jacparks ,

 

The formula does not have to be an if statement, it can be an index match or whatever formula that is appropriate to bring the actual values you would want to appear in that column, the preceding if statement is just to put a blank value if the row is occupied by the ingredient label i.e. focal...etc. So you could put:

=IF(OR($B9="Focal",$B9="Filler",$B9="Greenery",$B9="Line",$B9="Hardgoods / Rentals",ISBLANK(B9)),"",HLOOKUP($B$6,'Flower Counter'!$H$1:$AC$31,MATCH($B$3,'Flower Counter'!H:H,0),0))

 

Cheers 

Yea_So's avatar
Yea_So
Bronze Contributor
Jun 23, 2021

Hi jacparks ,

 

I forgot to remove the $ in row cell reference should not be $B$6, but should be $B6 in the HLOOKUP part:


=IF(OR($B6="Focal",$B6="Filler",$B6="Greenery",$B6="Line",$B6="Hardgoods / Rentals",ISBLANK(B6)),"",HLOOKUP($B6,'Flower Counter'!$H$1:$AC$31,MATCH($B$3,'Flower Counter'!H:H,0),0))

The Match reference refers to the Title of the card which is "Bridal Bouquet"

MATCH($B$3,'Flower Counter'!H:H,0), which returns the Bridal Bouquet row number in the "Flower Counter" Tab

 

attached is the updated file