Hi all,

I’m trying to enter this as Validation formula. My problem is it
evaluates to #N/A.
It had evaluated fine as a cell formula before shortening everything
to Named Formulas
Now, even as a regular cell formula, it doesn’t work.
Could it have something to do with Workbook vs Worksheet scoping?

The sheet for the validation is called RECIPES.
Any ideas?

P - QTY

=IF(OR($D2="",$E2=""),"",P_QTY_Formula/Pur_Unit_Wt.oz_Formula)
================================================== ==========

P_QTY_Formula

=(OFFSET((OFFSET(ItemStart,MATCH($C2,ItemColumn,0) ,0,1,1)),0,(MATCH
($E2,(OFFSET(ItemStart,MATCH($C2,ItemColumn,0),2,1 ,3)),0))+4,1,1))*$D
$2


Pur_Unit_Wt.oz_Formula

=INDEX(Pur_Unit_WT.oz,MATCH($C2,ItemColumn,0))

Thanks

max wrote:
Hi all,

Im trying to enter this as Validation formula. My problem is it
evaluates to #N/A.
It had evaluated fine as a cell formula before shortening everything
to Named Formulas
Now, even as a regular cell formula, it doesnt work.
Could it have something to do with Workbook vs Worksheet scoping?

The sheet for the validation is called RECIPES.
Any ideas?

P - QTY

=IF(OR($D2="",$E2=""),"",P_QTY_Formula/Pur_Unit_Wt.oz_Formula)
================================================= ===========

P_QTY_Formula

=(OFFSET((OFFSET(ItemStart,MATCH($C2,ItemColumn,0 ),0,1,1)),0,(MATCH
($E2,(OFFSET(ItemStart,MATCH($C2,ItemColumn,0),2, 1,3)),0))+4,1,1))*$D
$2

Pur_Unit_Wt.oz_Formula

=INDEX(Pur_Unit_WT.oz,MATCH($C2,ItemColumn,0))

Thanks

=========
Can you send the excel-file ?
This is too complex.

thnks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200906/1

Since I can't see the original message, I'll respond to this one.

I believe for validation lists, you need to use named ranges/formulas that
are scoped to the workbook.

HTH,
Barb Reinhardt

"pietbom via OfficeKB.com" wrote:

max wrote:
Hi all,

Im trying to enter this as Validation formula. My problem is it
evaluates to #N/A.
It had evaluated fine as a cell formula before shortening everything
to Named Formulas
Now, even as a regular cell formula, it doesnt work.
Could it have something to do with Workbook vs Worksheet scoping?

The sheet for the validation is called RECIPES.
Any ideas?

P - QTY

=IF(OR($D2="",$E2=""),"",P_QTY_Formula/Pur_Unit_Wt.oz_Formula)
================================================= ===========

P_QTY_Formula

=(OFFSET((OFFSET(ItemStart,MATCH($C2,ItemColumn,0 ),0,1,1)),0,(MATCH
($E2,(OFFSET(ItemStart,MATCH($C2,ItemColumn,0),2, 1,3)),0))+4,1,1))*$D
$2

Pur_Unit_Wt.oz_Formula

=INDEX(Pur_Unit_WT.oz,MATCH($C2,ItemColumn,0))

Thanks

=========
Can you send the excel-file ?
This is too complex.

thnks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200906/1

On Jun 19, 8:17*pm, Barb Reinhardt
wrote:
Since I can't see the original message, I'll respond to this one.

I believe for validation lists, you need to use named ranges/formulas that
are scoped to the workbook.

HTH,
Barb Reinhardt

"pietbom via OfficeKB.com" wrote:
max wrote:
Hi all,

I’m trying to enter this as Validation formula. *My problem is it
evaluates to #N/A.
It had evaluated fine as a cell formula before shortening everything
to Named Formulas
Now, even as a regular cell formula, it doesn’t work.
Could it have something to do with Workbook vs Worksheet scoping?

The sheet for the validation is called RECIPES.
Any ideas?

P - QTY

=IF(OR($D2="",$E2=""),"",P_QTY_Formula/Pur_Unit_Wt.oz_Formula)
================================================= ===========

P_QTY_Formula

=(OFFSET((OFFSET(ItemStart,MATCH($C2,ItemColumn,0 ),0,1,1)),0,(MATCH
($E2,(OFFSET(ItemStart,MATCH($C2,ItemColumn,0),2, 1,3)),0))+4,1,1))*$D
$2

Pur_Unit_Wt.oz_Formula

=INDEX(Pur_Unit_WT.oz,MATCH($C2,ItemColumn,0))

Thanks

I have no problems with my Range names. They work just fine.
I did try to search out info regarding formulas for this situation,
but they were relativley simple statements .

I thought I could cut down on the mega formula by Naming it and then
using it in the custom validation.
Are the any known limitations to defining a formula and then using it
in a custom validation set-up?


=========
Can you send the excel-file ?
This is too complex.

thnks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200906/1