Returning Values
 

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.

Returning Values
 

Hi Harlan,


Q1. Try something based on
=Sheet1!$A$1:OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$ A),)
using a column that has no gaps for the column reference in COUNTA(Sheet1!$A:$A).
Q2. More information needed on what C8 & D8 are and the structure of 'Unit Conversion'
Q3. You could use an IF test, along the lines of IF(ozVal=0,eaOffset,ozOffset) but, again, more information would be needed on your
data structures and existing formulae.

Cheers

--
macropod
[MVP - Microsoft Word]


"Harlan" wrote in message ...
| 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.