Recipe costing spreadsheet

%3CLINGO-SUB%20id%3D%22lingo-sub-3185669%22%20slang%3D%22en-US%22%3ERecipe%20costing%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3185669%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20a%20recipe%20costing%20spreadsheet.%20Currently%20what%20I%20have%20achieved%20is%20-%26nbsp%3B%3C%2FP%3E%3CP%3E-%20An%20Ingredients%20sheet%20with%20a%20breakdown%20of%20each%20ingredient%20to%20a%20price%20per%20gram.%26nbsp%3B%3C%2FP%3E%3CP%3E-%20Separate%20sheets%20that%20relate%20to%20different%20product%20areas%20(but%20are%20essentially%20the%20same%20format).%20For%20example%2C%20I%20have%20a%20bread%20sheet%20where%20all%20bread%20items%20will%20be%20costed%20(about%2010%20in%20total).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAt%20the%20moment%20I%20have%20worked%20out%20how%20to%20create%20a%20drop%20down%20menu%20(in%20the%20bread%20sheet)%20that%20can%20draw%20up%20any%20one%20of%20the%20ingredients%20listed%20(from%20the%20ingredients%20sheet).%20What%20I%20would%20like%20to%20happen%20is%20that%20when%20an%20ingredient%20is%20selected%20(in%20the%20bread%20sheet)%2C%20the%20price%20per%20gram%20of%20that%20ingredient%26nbsp%3B%20is%20listed%20in%20the%20following%20column%20(from%20there%20I%20can%20create%20separate%20columns%20with%20recipe%20amounts%20for%20each%20ingredient%2C%20times%20that%20by%20price%20per%20gram%2C%20add%20them%20all%20together%20and%20voila%2C%20total%20cost%20of%20one%20full%20recipe!)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20using%20the%20lookup%20function%20and%20have%20managed%20to%20get%20it%20to%20work%20for%20individual%20cells%20(having%20to%20input%20a%20separate%20formula%20each%20time)%2C%20but%20when%20I%20try%20to%20copy%20and%20paste%20the%20formula%20into%20other%20cells%20to%20make%20it%20work%20for%20all%20ingredients%2C%20I%20keep%20getting%20a%20%23N%2FA%20code%20back.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20I'm%20making%20sense%20here%2C%20as%20this%20has%20had%20me%20stumped%20all%20day.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3185669%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3185713%22%20slang%3D%22en-US%22%3ERe%3A%20Recipe%20costing%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3185713%22%20slang%3D%22en-US%22%3Ewow%2C%20really%20hard%20without%20seeing%20the%20sheet.%20a%20couple%20things%20to%20check%20is%20that%20the%20references%20are%20still%20valid%20after%20moving%2Fcopying%20the%20formula.%20For%20example%20if%20the%20reference%20is%20A1%3AA5%20and%20you%20copy%20that%20from%20B5%20to%20B4%20the%20new%20range%20will%20try%20to%20look%20at%20A0%3AA4%20which%20is%20invalid.%20You%20need%20to%20use%20the%20%24%20to%20'lock'%20the%20value%20A%241%3AA%245%20would%20lock%20rows%201%20-%205%20but%20if%20you%20copy%20from%20B5%20to%20C5%20would%20allow%20the%20Col%20to%20change%20from%20A%20to%20B.%20If%20you%20use%20%24A%241%3A%24A%245%20will%20lock%20both%20col%20and%20rows.%3CBR%20%2F%3EAlso%20check%20if%20a%20value%20is%20not%20found%20in%20the%20lookup.%20If%20you%20use%20XLOOKUP%20instead%20you%20can%20insert%20a%20if_not_found%20value%20but%20if%20you%20don't%20have%20Excel365%20then%20you%20might%20need%20to%20use%20IFERROR(...%2C%20%22alt%20value%22).%3CBR%20%2F%3Eotherwise%20maybe%20attach%20the%20sheet%20or%20give%20a%20link%20to%20it%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi, 

I am trying to create a recipe costing spreadsheet. Currently what I have achieved is - 

- An Ingredients sheet with a breakdown of each ingredient to a price per gram. 

- Separate sheets that relate to different product areas (but are essentially the same format). For example, I have a bread sheet where all bread items will be costed (about 10 in total). 

 

At the moment I have worked out how to create a drop down menu (in the bread sheet) that can draw up any one of the ingredients listed (from the ingredients sheet). What I would like to happen is that when an ingredient is selected (in the bread sheet), the price per gram of that ingredient  is listed in the following column (from there I can create separate columns with recipe amounts for each ingredient, times that by price per gram, add them all together and voila, total cost of one full recipe!)

 

I have been using the lookup function and have managed to get it to work for individual cells (having to input a separate formula each time), but when I try to copy and paste the formula into other cells to make it work for all ingredients, I keep getting a #N/A code back. 

 

I hope I'm making sense here, as this has had me stumped all day.  

1 Reply
wow, really hard without seeing the sheet. a couple things to check is that the references are still valid after moving/copying the formula. For example if the reference is A1:A5 and you copy that from B5 to B4 the new range will try to look at A0:A4 which is invalid. You need to use the $ to 'lock' the value A$1:A$5 would lock rows 1 - 5 but if you copy from B5 to C5 would allow the Col to change from A to B. If you use $A$1:$A$5 will lock both col and rows.
Also check if a value is not found in the lookup. If you use XLOOKUP instead you can insert a if_not_found value but if you don't have Excel365 then you might need to use IFERROR(..., "alt value").
otherwise maybe attach the sheet or give a link to it