Help building a meal planner

%3CLINGO-SUB%20id%3D%22lingo-sub-391365%22%20slang%3D%22en-US%22%3EHelp%20building%20a%20meal%20planner%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391365%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20looking%20for%20some%20help%20with%20a%20meal%20planner%20I'm%20trying%20to%20set%20up.%20I%20know%20how%20I%20want%20it%20to%20function%20however%20getting%20there%20is%20proving%20challenging.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22data.png%22%20style%3D%22width%3A%20681px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F100661iFFC5AB2BE0011B20%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22data.png%22%20alt%3D%22data.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%202%20(above)%20contains%20all%20of%20my%20data%20which%20is%20food%2C%20calories%20and%20macro-nutrients%20%3CSTRONG%3E%3CEM%3Eper%20gram.%3C%2FEM%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CEM%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22data%202.png%22%20style%3D%22width%3A%20680px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F100662i32B544BF73D5DC5C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22data%202.png%22%20alt%3D%22data%202.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FEM%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ESheet%201%20is%20the%20meal%20planner.%20I'll%20have%20drop%20down%20lists%20for%20each%20item%20of%20food%20per%20meal%2C%20and%20i'd%20like%20it%20to%20pull%20the%20data%20based%20on%20the%20food%20selected%20in%20the%20respective%20categories%20e.g%3A%20if%20chicken%20breast%20is%20selected%20it%20will%20populate%20the%20relevant%20protein%2C%20fat%20etc.%20from%20the%20data%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20also%20like%20the%20sheet%20to%20multiply%20the%20data%20based%20on%20the%20number%20that%20is%20input%20into%20the%20'grams'%20section.%3C%2FP%3E%3CP%3Ee.g.%20if%20300%20grams%20of%20chicken%20breast%20is%20selected%20it%20will%20multiply%20and%20populate%20the%20data%20by%20300.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20appreciate%20any%20help%20with%20this%20one%2C%20or%20even%20just%20advice%20on%20what%20functions%20i%20should%20be%20looking%20into%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-391365%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391385%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20building%20a%20meal%20planner%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391385%22%20slang%3D%22en-US%22%3EI%20don%E2%80%99t%20think%20so.%20In%20Sheet1!D2%2C%20COLUMN(B1)%20becomes%20COLUMN(C1)%20when%20copied%20to%20E2%2C%20COLUMN(D1)%20when%20copied%20to%20F2%2C%20and%20COLUMN(E1)%20when%20copied%20to%20G2.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391378%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20building%20a%20meal%20planner%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391378%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EHi%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20taking%20the%20time%20to%20help%20me%20out%20with%20this%20one.%20I%20really%20appreciate%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20using%20your%20formula%20however%20I%20can%20only%20seem%20to%20get%20it%20to%20use%20the%20data%20from%20the%20calories%20column%20even%20when%20the%20formula%20has%20been%20dragged%20into%20the%20other%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391376%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20building%20a%20meal%20planner%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391376%22%20slang%3D%22en-US%22%3EThe%20formula%20in%20Sheet1!D2%20should%20be%3A%3CBR%20%2F%3E%3D%24C2*%3CBR%20%2F%3EVLOOKUP(%24B2%2C%3CBR%20%2F%3ESheet2!%24A%3A%24E%2C%3CBR%20%2F%3ECOLUMN(B1)%2C0)%3CBR%20%2F%3EI%20inadvertently%20forgot%20to%20multiply%20by%20%24C2%20in%20my%20earlier%20formula.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391375%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20building%20a%20meal%20planner%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391375%22%20slang%3D%22en-US%22%3EIn%20Sheet1!D2%2C%20Enter%20this%20formula%3A%3CBR%20%2F%3E%3DVLOOKUP(%24B2%2C%3CBR%20%2F%3ESheet2!%24A%3A%24E%2C%3CBR%20%2F%3ECOLUMN(B1)%2C0)%3CBR%20%2F%3EThen%2C%20you%20may%20copy%20the%20formula%20down%20rows%20and%20across%20columns.%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hey all,

 

I'm looking for some help with a meal planner I'm trying to set up. I know how I want it to function however getting there is proving challenging.

 

data.png

 

Sheet 2 (above) contains all of my data which is food, calories and macro-nutrients per gram.

 

data 2.png

Sheet 1 is the meal planner. I'll have drop down lists for each item of food per meal, and i'd like it to pull the data based on the food selected in the respective categories e.g: if chicken breast is selected it will populate the relevant protein, fat etc. from the data sheet.

 

I'd also like the sheet to multiply the data based on the number that is input into the 'grams' section.

e.g. if 300 grams of chicken breast is selected it will multiply and populate the data by 300.

 

I'd appreciate any help with this one, or even just advice on what functions i should be looking into etc.

 

Cheers. 

4 Replies
In Sheet1!D2, Enter this formula:
=VLOOKUP($B2,
Sheet2!$A:$E,
COLUMN(B1),0)
Then, you may copy the formula down rows and across columns.
Highlighted
The formula in Sheet1!D2 should be:
=$C2*
VLOOKUP($B2,
Sheet2!$A:$E,
COLUMN(B1),0)
I inadvertently forgot to multiply by $C2 in my earlier formula.
Highlighted

@Twifoo 
Hi there,

 

Thanks for taking the time to help me out with this one. I really appreciate it.

 

I've tried using your formula however I can only seem to get it to use the data from the calories column even when the formula has been dragged into the other cells.

 

Any ideas?

 

Thanks

Highlighted
I don’t think so. In Sheet1!D2, COLUMN(B1) becomes COLUMN(C1) when copied to E2, COLUMN(D1) when copied to F2, and COLUMN(E1) when copied to G2.