Hi Folks,
I had noticed an earlier thread referring to the creation of a UDF.
I have created UDF's on simpler, much less lengthy formulas, but I'd like to know if there's an easier method when applying it to a formula, such as listed below?
Is there another avenue other than writing Application.WorksheetFunction for each instance of MATCH?
Also, how would these nested formulas actually transpose to VBA, if its even possible at all to do so?
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
=IF(OR($D10="",$E10=""),"",
(IF(OFFSET(IngredientStart,MATCH($C10,IngredientColumn,0),1,1,1)="ml",
((OFFSET((OFFSET(IngredientStart,MATCH($C10,IngredientColumn,),0,1,1)),0,(MATCH($E10,(OFFSET(IngredientStart,MATCH($C10,IngredientColumn,0),2,1,3)),0))+4,1,1))*mlPerOz)
*$D10/INDEX(Pur_Unit_WT.oz,MATCH($C10,IngredientColumn,0)) ,
(IF(OFFSET(IngredientStart,MATCH($C10,IngredientColumn,0),1,1,1)="L",
((OFFSET((OFFSET(IngredientStart,MATCH($C10,IngredientColumn,0),0,1,1)),0,(MATCH($E10,(OFFSET(IngredientStart,MATCH($C10,IngredientColumn,0),2,1,3)),0))+4,1,1))*LperOz)
*$D10/INDEX(Pur_Unit_WT.oz,MATCH($C10,IngredientColumn,0)) ,
(OFFSET((OFFSET(IngredientStart,MATCH($C10,IngredientColumn,0),0,1,1)),0,(MATCH($E10,(OFFSET(IngredientStart,MATCH($C10,IngredientColumn,0),2,1,3)),0))+4,1,1))
*$D10/INDEX(Pur_Unit_WT.oz,MATCH($C10,IngredientColumn,0)))))) )
+++++++++++++++++++++++++++++++++++++++++++++++++
This is all one formula and it works quite nicely.
There are only three cells being referenced.
Is it possible to create a UDF along this line: ‘ =Ingredient(Cell 1, Cell 2 ,Cell 3) ‘
All of the Range References are on the “IngredientsList” sheet
Thanks for your time.
Mark
Bookmarks