Valid data allow to ref sheet

[Sirfyaad]

I want that only that value should be allowed which is in the ref sheet, otherwise no other value should be allowed. Could you please guide me on how to achieve this. 

allow list ref sheet

When i used this expression showing error .

ISNOTBLANK(
  FILTER(
    "FLYER LIST",
    OR(
      AND(
        NOT(IN([_THISROW].[AWB NO], SELECT(FLYER LIST[START FLYER NO], STARTSWITH([_THISROW].[AWB NO], [START FLYER NO])))),
        IN([_THISROW].[AWB NO], SELECT(FLYER LIST[END FLYER NO], ENDSWITH([_THISROW].[AWB NO], [END FLYER NO]))),
        ENDSWITH([END FLYER NO], [_THISROW].[AWB NO])
      ),
      AND(
        IN([_THISROW].[AWB NO], SELECT(FLYER LIST[START FLYER NO], STARTSWITH([_THISROW].[AWB NO], [START FLYER NO]))),
        STARTSWITH([START FLYER NO], [_THISROW].[AWB NO])
      )
    )
  )
)

[Sirfyaad]

Need help please @AleksiAlkio 

[Sirfyaad]

Need help please @Suvrutt_Gurjar 

[Suvrutt_Gurjar]

As usual you need  to give more details, but based on understanding so far, you could try:

OR(

IN(LEFT([AWB NO] ,3), FLYER LIST[START FLYER NO]) ,

IN(LEFT([AWB NO] ,4), FLYER LIST[START FLYER NO]) ,

IN(RIGHT([AWB NO] ,2), FLYER LIST[END FLYER NO]) ,

IN(RIGHT([AWB NO] ,4), FLYER LIST[END FLYER NO]) ,

IN(RIGHT([AWB NO] ,5), FLYER LIST[END FLYER NO])

)

Edit: made minor changes to the expression based on the screenshots examples shared by you.

[Sirfyaad]

Hi @Suvrutt_Gurjar 

This expression is working but I want ENDSWITH()

STARTSWITH()

Base on this expression. 

[Suvrutt_Gurjar]

I believe STARTSWITH() and ENDSWITH() is not convenient to use in this use case and the expression may become more complex with STARTSWITH() and ENDSWITH()

[Sirfyaad]

---

@Suvrutt_Gurjar wrote:

OR(

IN(LEFT([AWB NO] ,3), FLYER LIST[START FLYER NO]) ,

IN(LEFT([AWB NO] ,4), FLYER LIST[START FLYER NO]) ,

IN(RIGHT([AWB NO] ,2), FLYER LIST[END FLYER NO]) ,

IN(RIGHT([AWB NO] ,4), FLYER LIST[END FLYER NO]) ,

IN(RIGHT([AWB NO] ,5), FLYER LIST[END FLYER NO])

)

---

Hi @Suvrutt_Gurjar The current expression works, but it's impractical for more than 150 [Start Flyer No] with [End Flyer No] set at 125. Manually using right() and left() is time-consuming. Can you suggest a more efficient solution or trick?

[Suvrutt_Gurjar]

There can be any number of such options, as long as they are within the specified length of 2, 3, 4, or 5 , it should not be a problem. Currently start lengths checked are for 3 nd 4 characters and for end lengths 2, 4 and 5 characters. If there are a few additinal character lengths you can add a few sub expressions. For example the below expression

OR(

IN(LEFT([AWB NO] ,2), FLYER LIST[START FLYER NO]) ,

IN(LEFT([AWB NO] ,3), FLYER LIST[START FLYER NO]) ,

IN(LEFT([AWB NO] ,4), FLYER LIST[START FLYER NO]) ,

IN(LEFT([AWB NO] ,5), FLYER LIST[START FLYER NO]) ,

IN(RIGHT([AWB NO] ,2), FLYER LIST[END FLYER NO]) ,

IN(RIGHT([AWB NO] ,3), FLYER LIST[END FLYER NO]) ,

IN(RIGHT([AWB NO] ,4), FLYER LIST[END FLYER NO]) ,

IN(RIGHT([AWB NO] ,5), FLYER LIST[END FLYER NO])

)

should check for all [Start Flyer No] and [End Flyer No]  that are 2 to 5 character lengths.

[Sirfyaad]

I agree with you, but please understand my concern. I have a large quantity of [start Flyer No] to [End Flyer No], and processing it will take considerable time, making the task quite extensive.

[Sirfyaad]

Hi @Suvrutt_Gurjar  I am waiting for your solution please help.

[Vincent_P]

Hello, 

Why don't you split your field in three fields in your form

user select the start and the end with combo and write the intermediate value. Then you have a forth field that concatenate all. and you don't need to test it.

[Sirfyaad]

How 

[Vincent_P]

Like this : 

in this capture, AWB is a virtual column with this formula : [awb prefix]&[awb middle]&[awb suffix]

[Izzat_Safrah]

Try this expression it will work good

IFS(
ISNOTBLANK(
INTERSECT(
LIST(  RIGHT([AWB NO],2),
       RIGHT([AWB NO],3),
       RIGHT([AWB NO],4),
       RIGHT([AWB NO],5)),  FLYER LIST[END FLYER NO] )), TRUE,
       
ISNOTBLANK(
INTERSECT(
LIST(  LEFT([AWB NO],2),
       LEFT([AWB NO],3),
       LEFT([AWB NO],4),
       LEFT([AWB NO],5)),  FLYER LIST[START FLYER NO] )),TRUE ,
         
FALSE , FALSE 

  )