Recipe Cost Form with Conversions and Drop Down Menu

Hello Dracoko4757 and Welcome to Excel Forum.
Not quite sure that I understand how this should work. It sounds as if you want to choose LBS in say cell E9 and then have the cell display ounces. Further it sounds as if you want the amount in D9 to represent the number of LBS or convert from the number of pounds to the number of ounces in the same cell. I don't believe this can be accomplished using formulas, however, I can see how you could put 10 in D9 and choose LBS in E9 then have F9 display 160 and G9 display ounces. Perhaps we could get a clearer understanding of what you want if you could manually mock up a recipe so that we may attempt to automate the process.
Please upload a sample workbook (not a picture or pasted copy) by clicking on the GO ADVANCED button below the Quick Reply window and then scrolling down to Manage Attachments to open the upload window.
Let us know if you have any questions.

Not to worry. I don't know much (anything really) about being a chef, so you may have to help me understand the overall goal of the Master recipe sheet.
Lets start by thinking about how this would be done by hand. I feel that the SW code would be written followed by the other information through the point where the UOM is written. If at that point the original units (LB) were erased and replaced with Units of Usage (Oz) it might not be understandable as to what the number in the unit price column referred?
Along those lines a small table is placed on the Data sheet listing the UOM and then UOU (units of usage), based on the information in post #1. There is also a column for conversion factor.
On the master sheet column G (which has been added) displays the UOU using: =VLOOKUP(F9,Data!K$2:L$5,2,FALSE)
The formula in the 'Extension' column (I) takes the units listed in column H, assumed to be Oz, and multiplies by the conversion factor and the amount per UOM (LBS in this case), using: =H9*VLOOKUP(F9,Data!K$2:M$5,3,FALSE)*E9
The number in I9 represents the cost of 15 ounces of Ribeye. If that isn't what it should represent, or not the correct value please let us know.
Let us know if you have any questions.

This is great! A lot more than I could have done by myself. The problem with this is that this works if all measurements are in ounces. I need the UOU and the Usage to work together so that if I put 16lbs in the Usage column it calculates for LB, making I9 = 88.48. It would be the same for eggs, if I cost the egg in the data sheet to a unit of measure as case price, 25.6 in the E column and then .16 each for one egg I want to be able to put DZ in the UOU and 12 in the USAGE column and have this calculate 12 dozen eggs, or .16*144


like, if=MASTER G9=LB then VlookupXXXXDATA F2
if=MASTER G9=oz then VlookXXXXXDATA F2/16

but all that calculating from only what is placed in the UOU and USAGE column.

This workbook would assume that I put in all the case price and whatever makes sense for the extended price. Egg is by case and each, beef is by case and pound. I want to make it so the Chefs under me can enter in beef as OZ 12 and bam, the price calculates from ounces. But if another chef comes along and writes a much large recipe then they can enter beef as LB 40 and it calculates for pound price. The chefs base their costing on what the description says, if they use chocolate in KG they wouldn't ever put in EA so if I fill in the pricing for them then they don't have to think and can keep making awesome food in the restaurants.

FYI if you are ever in Vegas dinner is on me at any restaurant in our hotel!

See if this moves closer to what you need.
To limit the possibility of mistakes, data validation is set up for column G which links to the values in columns Q:T on the Data sheet. The only values that will be in the list are 'CS' (case), the UOM unit and the other unit allowable for the UOM unit listed at the top of the column (I hope that makes sense).
The data validation formula is: =INDIRECT(F9)
The formula for the extension is: =IF(G9="cs",D9*H9,IF(G9=F9,E9*H9,H9*VLOOKUP(F9,Data!K$2:M$5,3,FALSE)*E9))
Note that the above formula works assuming UOM (LB) is only converted to either CS, LB or Oz. If it becomes necessary to say convert LB to g or Kg the conversion table will need reworking and the formula will need to be modified.
Let us know if you have any questions.
P.S. Appreciate the dinner offer, perhaps I will add Vegas to my bucket list.

It seemed to me that there is a possibility the a recipe could have UOM in Kg and UOU in either LB's, Oz's or g's. I went ahead and amended the named range (Kg) to include all three of those and rewrote the formula in cell H9 to read:

Formula:

Please Login or Register  to view this content.

Admittedly the formula may be a bit hard to understand, however the bottom line is that it adjusts for both the UOM and UOU values. Play with the table on the master sheet a bit and let me know if it needs some tweaking. I may be away for a few days, but will take a look when I get back. It is also possible that another contributor will have an idea on a different/better way to accomplish what you want.
Let us know if you have any questions.