Forum Discussion
making a formula for a drop down list
Hello everyone
I am trying to make a calculator giving nutrition information from different baby milk formulas. The information for this is in a table in a separate excel sheet, Sheet 2 The milk formulas can be chosen from a drop down list in the main Calculator sheet. How do I create a formula which will take the right values for the type of milk chosen from the table in Sheet 2
4 Replies
Hi,
I named the range in Sheet 2 "Data"
I added a sample record in Row # 3 using your drop list
I created the function in cell D 3 as follows:
=VLOOKUP($C3,Data,COLUMNS($J$1:K1),0)/100*($B3/$A3)
Copy the function to the left and down
Test using different items from the drop list
You may consider wrapping it in an IFERROR function
=IFERROR(VLOOKUP($C3,Data,COLUMNS($J$1:K1),0)/100*($B3/$A3),"")
Hope that helps
File attached
You can use VLOOKUP for this, for example in C2:
=VLOOKUP(C2,Sheet2!$A$3:$F$9,2,FALSE)/100*B2/A2This formula can be filled down. See the attached version.
Thank you. That was really quick. I can see that you have chosen the range in Sheet 2!$A$3:$F$9. However in the drop down list, I cannot see the last 2 names of the milk formulas, Nutriprem 1and Nutriprem 2. Any thoughts?
In the workbook that you attached to the first post, the source of the data validation dropdown was set to Sheet 2!$A$3:$F$7. I hadn't changed that.
