I'm trying to create an excel file for recipes that I have. I'm using one sheet to list out the ingredients and total up the calories and various macro nutrients. On the other sheet, I have a list of ingredients. Since the numbers change depending on what brand I use for the recipe, I'm trying to create a drop down list in the brand box of of the recipes sheet. I've attached a couple of images to try to explain what I mean. When I select whatever brand on the recipes sheet, I want it to populate with whatever numbers are for that specific brand. I've tried using data validation, selecting the entire row that I want in the other sheet, but I keep getting an error saying You may not use reference operators or array constants for Data Validation. I've also tried defining the name in formulas, but the only drop down that creates is the name of the group without changing any of the numbers.

Sorry if this is a bit confusing. If I need to clarify let me know.

I'm not sure if i understand you correctly, but if I do, I would look vlookup function.

Two worksheets, named "Recipes" and "Ingredients"
In the Ingredients sheet you have
A = Ingredient name (this column is actually uncritical)
B = Ingredient Brand and unit. For example: ("Pepsi, 100ml")
C=Calories
D (etc) rest of the parameters.

Now in Recipes you have:
A=Meal
B=Name of recipe
C=Ingredient/Unit. This can be a dropdown list (Data Validation => List => Ingredients!B2:B999)
D=Quantity. So if you need 200ml of Pepsi, you would put "2" here
E=Calories. For E2 the formula will be For the columns F to whatever will be the rest of your parameters (in the exact same order!) and you can just copy and paste the formula from E2 to the rest of your sheet.

Now you use the row filters in the top row as you do in your pics to filter out the things you want to see