Forum Discussion
Recipe costing spreadsheet
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
