New Contributor

# 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

# Re: making a formula for a drop down list

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.

# Re: making a formula for a drop down list

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

# Re: making a formula for a drop down list

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?

# Re: making a formula for a drop down list

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.