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.