Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct issues | Excel Worksheet Functions | |||
Looking up data in a column, then returning values of respective row | Excel Discussion (Misc queries) | |||
Formula for Returning values in another spreadsheet | Excel Worksheet Functions | |||
vlookup returning multiple values | Excel Worksheet Functions | |||
Returning Descending Values | Excel Worksheet Functions |