Nov 01 2020 01:54 PM
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
Nov 01 2020 02:05 PM
You can use VLOOKUP for this, for example in C2:
=VLOOKUP(C2,Sheet2!$A$3:$F$9,2,FALSE)/100*B2/A2
This formula can be filled down. See the attached version.
Nov 01 2020 02:18 PM
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
Nov 01 2020 02:26 PM
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?
Nov 01 2020 03:13 PM
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.