making a formula for a drop down list

Copper Contributor

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

@alokcardiff 

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.

@alokcardiff 

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

 

@Hans Vogelaar 

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?

@alokcardiff 

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.