• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation Error??

Hi,
why when I try to implement formula mentioned in H7 as DV in E7 onward I always get wrong message. If you try to change cell D7 to 13 then the wrong message will pop up while same formula which is in cell H7 GIVES YOU TRUE.

thank you
 

Attachments

  • BOOK2341.xlsx
    29 KB · Views: 10
Your Data Validation Setting set to custom and has formula, if you want to remove it:
1. Clik Data Validation
2. Find Setting Tab
3. Change "Custom" with Any Value, and then click OK

Or just block with your mouse from D2:D29, and follow step above

Cheers
 
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
 
Hi
@NARAYANK991 thank you much but by by changing highlighted portion to $Z8, the problem still persist. if it is true, the date is correct and if it is false the data is an error. any suggestion.

@azumi thank you much for your reply but i could not figure out how to change my data validation setting . i did try to follow the steps but with no success.
thanks
 

Attachments

  • BOOK2341.xlsx
    29.7 KB · Views: 2
Hi Nader ,

I did not say that by changing the Z8 to either $Z8 or Z$8 your problem would be solved. I merely pointed out that you needed to use these versions based on which one was according to your requirements.

Now to the problem that you are facing ; all I can say is that you need to explain what you want the DV to do , instead of asking what is wrong ; I don't think many of us would know what you want to do ; if you can explain that , it may be easier to point out where the problem lies.

Narayan
 
Hi
what i am intending to do with this DV is the following
based on the aircraft registration e.g JYBAA which belongs to B787 aircraft type
i want to decide the loading possibilities from LOADING_POSITION TABLE and each loading possibility should be entered only once

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

after that comes other restrictions
-1 loading position 1 and 4 can be entered or repeated many times if aircraft type is either of E75 or E95

,IF(OR(INDEX($BJ$6:$BJ$43,MATCH($C$5,a.reg,0))="e95",INDEX($BJ$6:$BJ$43,MATCH($C$5,a.reg,0))="e75"),D7

-2 loading position is 51 can be entered or repeated many times if aircraft type is either of A330,A340,A340,A319,A320,A321

,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))

3- any loading position mentioned in column D7 should not be avaiable or mentioned in columns U7:Z42

COUNTIF(U$8:Z8,D7)<1)

Thank you
 
Back
Top