making a formula for a drop down list

%3CLINGO-SUB%20id%3D%22lingo-sub-1843057%22%20slang%3D%22en-US%22%3Emaking%20a%20formula%20for%20a%20drop%20down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1843057%22%20slang%3D%22en-US%22%3E%3CP%3E%3CEM%3EHello%20everyone%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3EI%20am%20trying%20to%20make%20a%20calculator%20giving%20nutrition%20information%20from%20different%20baby%20milk%20formulas.%20The%20information%20for%20this%20is%20in%20a%20table%20in%20a%20separate%20excel%20sheet%2C%20Sheet%202%20The%20milk%20formulas%20can%20be%20chosen%20from%20a%20drop%20down%20list%20in%20the%20main%20Calculator%20sheet.%20How%20do%20I%20create%20a%20formula%20which%20will%20take%20the%20right%20values%20for%20the%20type%20of%20milk%20chosen%20from%20the%20table%20in%20Sheet%202%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1843057%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1843075%22%20slang%3D%22en-US%22%3ERe%3A%20making%20a%20formula%20for%20a%20drop%20down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1843075%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F853736%22%20target%3D%22_blank%22%3E%40alokcardiff%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20use%20VLOOKUP%20for%20this%2C%20for%20example%20in%20C2%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DVLOOKUP(C2%2CSheet2!%24A%243%3A%24F%249%2C2%2CFALSE)%2F100*B2%2FA2%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThis%20formula%20can%20be%20filled%20down.%20See%20the%20attached%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1843080%22%20slang%3D%22en-US%22%3ERe%3A%20making%20a%20formula%20for%20a%20drop%20down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1843080%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F853736%22%20target%3D%22_blank%22%3E%40alokcardiff%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EI%20named%20the%20range%20in%20Sheet%202%20%22Data%22%3C%2FP%3E%0A%3CP%3EI%20added%20a%20sample%20record%20in%20Row%20%23%203%20using%20your%20drop%20list%3C%2FP%3E%0A%3CP%3EI%20created%20the%20function%20in%20cell%20D%203%20as%20follows%3A%3C%2FP%3E%0A%3CP%3E%3DVLOOKUP(%24C3%2CData%2CCOLUMNS(%24J%241%3AK1)%2C0)%2F100*(%24B3%2F%24A3)%3C%2FP%3E%0A%3CP%3ECopy%20the%20function%20to%20the%20left%20and%20down%3C%2FP%3E%0A%3CP%3ETest%20using%20different%20items%20from%20the%20drop%20list%3C%2FP%3E%0A%3CP%3EYou%20may%20consider%20wrapping%20it%20in%20an%20IFERROR%20function%3C%2FP%3E%0A%3CP%3E%3DIFERROR(VLOOKUP(%24C3%2CData%2CCOLUMNS(%24J%241%3AK1)%2C0)%2F100*(%24B3%2F%24A3)%2C%22%22)%3C%2FP%3E%0A%3CP%3EHope%20that%20helps%3C%2FP%3E%0A%3CP%3EFile%20attached%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted

@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.

Highlighted

@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

 

Highlighted

@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?

Highlighted

@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.