Drop down list to auto-populate fields and multiplied by another field

looking at you example file i dont see wher ethe drop down would be, and im a bit confused as to how the user would use the sheet.. could you include your desired result as well, that might help.

Originally Posted by NBVC

So can Food 1 be selected multiple times? and if so, we need to multiply by the total of all Food 1 selection quantities?

Technically I suppose it could be, however I don't need it to. Ideally the person using the sheet would only select Food 1 a single time and then just use the multiplier.

Originally Posted by NBVC

Also,

If you select Food 2 and the Quantity for Food 1 is 10 and for Food 2 it is 5, what should the numbers in the Food 2 row look like in bottom table?

I just modified the sheet, this might help more. Food 1 is just a selection of the first item, Food 2 the first two items, etc. I have put some sample numbers in there to attempt to explain. See amended file. Thank you!! example.xlsx

Originally Posted by DGagnon

looking at you example file i dont see wher ethe drop down would be, and im a bit confused as to how the user would use the sheet.. could you include your desired result as well, that might help.

The drop down would be under selection. The user would manually input a whole number for quantity. See the most recent post for a file for a better example. Thank you!! I'm banging my head against the wall trying to figure this out.

if i understand you correctly this should work, i created a sum for each line, then replicated it across the food items, check out the example.

Originally Posted by NBVC

To be safe, I'd use in E8:

=SUMIF($B$2:$B$5,$C8,$C$2:$C$5)

copied down.

Works great! I went ahead and made the drop down menu and it works as well.

Lastly, what if I want to define Food 1 in a separate location. IE: Food 1 may change very often, and I will be dealing with 2000+ variables. I want to be able to define Food 1 in a separate location and still use the modifier. I attached the sheet again with the drop down list, and a text example of what I'm looking to do. Thank you both again!!! Here: end state request.xlsx

are you meaning that the numbers that are selected for Food 1 would change, so instead of just having totals on Pizza, it may be for example Pizza and Milk that need to be totaled?

Originally Posted by NBVC

See if the attached helps.

I am assuming only one dropdown selection per your sample.

I separated you data into separate cells so they can be more easily looked up.... and then applied formula in E8:

=IF(AND($C8=$B$2,ISNUMBER(MATCH(E$7,INDEX($T$6:$AD$9,MATCH($B$2,$S$6:$S$9,0),0),0))),$C$2,"")

copied down and across the table.

you can make the T6:AD9 and S6:S9 ranges as wide as you want to include more possible "food"

This is perfect!! One last thing if you don't mind, what would the code change be if I wanted to have X number of boxes?

Let me clarify. If I was X+ more down down boxes with the same options so people can select Food 1 and Food 4 at the same time.

Perfect!!! You are awesome!! Heading to the Lupus Canada page right now to donate!!

Thank you so much for the help!!

Marked as solved!!