SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2465607%22%20slang%3D%22en-US%22%3EExcel%20-%20Fill%20in%20values%20from%20column%20and%20row%20based%20on%20specific%20criteria%20to%20make%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2465607%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20a%20florist%20and%20I%20make%20a%20spreadsheet%20with%20the%20item%20that%20I%20am%20building%20(Ex.%20Bouquet)%2C%20I%20specify%20the%20flowers%20I%20am%20using%20at%20the%20top%20then%20I%20fill%20to%20the%20right%20with%20the%20number%20of%20stems%20I%20want%20in%20each%20item%20(bouquet).%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22jacparks_0-1624236610993.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F290212i8D6B5E13392999C2%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22jacparks_0-1624236610993.png%22%20alt%3D%22jacparks_0-1624236610993.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20this%20to%20then%20transfer%20to%20a%20recipe%20sheet%20where%20I%20have%20a%20recipe%20box%20for%20each%20Item%20(bouquet)%20and%20the%20recipe%20to%20specify%20the%20flower%20and%20how%20many%20stems%20of%20that%20flower%20for%20that%20specific%20Item%20(bouquet).%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20what%20I%20have%20laid%20out%20for%20the%20recipe%20sheet.%26nbsp%3B%20I%20cant%20figure%20out%20how%20to%20get%20the%20data%20from%20the%20table%20to%20the%20recipe%20card%20with%20only%20the%20flowers%20and%20quantities%20that%20are%20in%20that%20specific%20item.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22jacparks_1-1624236925152.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F290213iB0332FF92C30DB8C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22jacparks_1-1624236925152.png%22%20alt%3D%22jacparks_1-1624236925152.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20hope%20this%20makes%20sense.%26nbsp%3B%20Thanks%20for%20your%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2465607%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2465649%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Fill%20in%20values%20from%20column%20and%20row%20based%20on%20specific%20criteria%20to%20make%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2465649%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1083756%22%20target%3D%22_blank%22%3E%40jacparks%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20share%20your%20spreadsheet%20these%20techie%20types%20will%20flock%20around%20here%20to%20flex%20their%20brains%20around%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Echeers%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

jacparks_0-1624236610993.png

 

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. 

jacparks_1-1624236925152.png

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

 

20 Replies

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 Here it is Attached. 

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

It’s 3 stems of the alstroemeria in the bridal bouquet.

Hi @jacparks,

 

I added some named ranges:

Yea_So_0-1624387424280.png

 

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

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

Yea_So_2-1624387648357.png

In

Yea_So_3-1624387740583.png

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)

 

Yea_So_4-1624387935266.png

 

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

 

Cheers

 

 

 

 

Thanks so much! I won’t normally have the same two flowers I was just making sure they would auto fill. I really appreciate your help!
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, "")

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 

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

Hi @jacparks,

 

Thought I should make it look prettier:

Yea_So_0-1624476963933.png

 

Cheers

@jacparks 

 

By the way...

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

Yea_So_1-1624487443368.png

 

Yea_So_0-1624487411829.png

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

best response confirmed by allyreckerman (Microsoft)
Solution

@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

Create a searchable drop-down list in Excel using the FILTER function. This is the easiest way to set up a searchable list. Download the file used in the vid...
Oh my gosh you're the best! it's perfect thank you so much!

@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?

 

SharedScreenshot.jpg

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

We would have to convert the drop down list into a searchable one so you can just type the first few characters i.e. Filler and it will narrow the selection down to 1 or 5 depending on what flowers you have that have that combination of spelling
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.

Hi @jacparks,

 

The ingredients list is functional to the entire scheme of things, especially when you will be sitting with a client during the selection.  Why don't you take a look at this workbook.

 

In the ingredients list, if you put a space character on the cell adjacent to the drop down, it will change the drop down menu to the Ingredients header, and when you make a selection will format the header with its appropriate highlighting, and on top it will display the ingredient header in bold.  Let me know your thoughts.

 

cheers