Recipe costing spreadsheet

[Krait220]

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.  

[#2]

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