Ok, here's the deal:
I have created a recipe costing sheet that is linked to my product list in
the same workbook. Right now I am using defined names and validation rules
to create dropdown lists in the costing sheet to select the products. What I
have managed to do is that when a product is selected, the rest of the cells
across the row should fill in. I've gotten almost all of them to work. The
product list has about 533 rows in it. I actually have three questions.
1) Is there a way to name the range so that as the list expands or
contracts, nothing will be out of wack.
2) This is what has been throwing me lately. I have a column in the
costing sheet called "Oz." where all units will be converted to "oz." I
created a separate sheet that just had a list of the units and their
conversion factors. I run into a problem when I try to convert a product
that is an "each." Example: how much does 1 onion weigh? I tried the
following formula, but it doesn't seem to be working. Any suggestions?
=IF(D8='Unit Conversion'!A3:A10,VLOOKUP(D8,'Unit
Conversion'!A3:B11,2,FALSE),VLOOKUP(A8,'Food Inventory
Master'!$A$13:$C$533,3,FALSE))*C8
3) Finally, the last column of the costing sheet should just multiply the
cost per unit (usually "oz.") times the ounces. What I want it to do is that
if the units being used is not ounces, but "ea." I want it to multiply by a
different column. Is there a way to do that?
Thanks in advance. And any and all suggestions are welcome.