Formula Names not working

[max]

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

 

[pietbom via OfficeKB.com]

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

=========
Can you send the excel-file ?
This is too complex.
(e-mail address removed)
thnks

 

[Barb Reinhardt]

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

 

[max]

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

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.
(e-mail address removed)
thnks