Hi Nader ,
Your formula for position_restriction is :
=AND(COUNTIF(INDEX(loading_position,,MATCH(INDEX(Sheet2!$BJ$6:$BJ$43,MATCH(Sheet2!$C$5,a.reg,0)),AIRCRAFT_TYPE_POSOTION,0)),Sheet2!D7)=1,IF(OR(INDEX(Sheet2!$BJ$6:$BJ$43,MATCH(Sheet2!$C$5,a.reg,0))="e95",INDEX(Sheet2!$BJ$6:$BJ$43,MATCH(Sheet2!$C$5,a.reg,0))="e75"),Sheet2!D7,IF(AND(OR(INDEX(Sheet2!$BJ$6:$BJ$43,MATCH(Sheet2!$C$5,a.reg,0))="A340",INDEX(Sheet2!$BJ$6:$BJ$43,MATCH(Sheet2!$C$5,a.reg,0))="A330",INDEX(Sheet2!$BJ$6:$BJ$43,MATCH(Sheet2!$C$5,a.reg,0))="b787",INDEX(Sheet2!$BJ$6:$BJ$43,MATCH(Sheet2!$C$5,a.reg,0))="A321",INDEX(Sheet2!$BJ$6:$BJ$43,MATCH(Sheet2!$C$5,a.reg,0))="A320",INDEX(Sheet2!$BJ$6:$BJ$43,MATCH(Sheet2!$C$5,a.reg,0))="A319"),Sheet2!D7<>51),COUNTIF(Sheet2!D$7:D7,Sheet2!D7)=1,Sheet2!D7)),Sheet2!C7<>"",COUNTIF(Sheet2!U$8:Z8,Sheet2!D7)<1)
There are one position in this formula , where the $ sign may or may not be used ; you need to decide which of them is more correct. I have highlighted it in red.
However , apart from this , you should use $ signs more appropriately , so that when you look at the formula in the Name Manager , it shows the correct formula ; at present , the way the formula is written , without the $ signs , you will see a totally different version of it depending on where the cursor is located.
So , preferably , change the formula in the Refers To box to the following :
=AND(COUNTIF(INDEX(loading_position,,MATCH(INDEX(Sheet2!$BJ$6:$BJ$43,MATCH(Sheet2!$C$5,a.reg,0)),AIRCRAFT_TYPE_POSITION,0)),Sheet2!$D7)=1,IF(OR(INDEX(Sheet2!$BJ$6:$BJ$43,MATCH(Sheet2!$C$5,a.reg,0))="E95",INDEX(Sheet2!$BJ$6:$BJ$43,MATCH(Sheet2!$C$5,a.reg,0))="E75"),Sheet2!$D7,IF(AND(OR(INDEX(Sheet2!$BJ$6:$BJ$43,MATCH(Sheet2!$C$5,a.reg,0))="A340",INDEX(Sheet2!$BJ$6:$BJ$43,MATCH(Sheet2!$C$5,a.reg,0))="A330",INDEX(Sheet2!$BJ$6:$BJ$43,MATCH(Sheet2!$C$5,a.reg,0))="B787",INDEX(Sheet2!$BJ$6:$BJ$43,MATCH(Sheet2!$C$5,a.reg,0))="A321",INDEX(Sheet2!$BJ$6:$BJ$43,MATCH(Sheet2!$C$5,a.reg,0))="A320",INDEX(Sheet2!$BJ$6:$BJ$43,MATCH(Sheet2!$C$5,a.reg,0))="A319"),Sheet2!$D7<>51),COUNTIF(Sheet2!$D$7:$D7,Sheet2!$D7)=1,Sheet2!$D7)),Sheet2!$D7<>"",COUNTIF(Sheet2!$U$8:$Z8,Sheet2!$D7)<1)
As far as the highlighted portion is concerned , you need to decide whether the second part should be $Z8 or $Z$8. I think $Z8 is OK.
Secondly , you have named it :
position_restriction
Does this mean that if this is TRUE , then the data entry is an error ? Or is the data entry invalid if this is FALSE ? I have not taken the trouble to understand this formula , which is why I am asking this question.
Narayan