I have duplicated from two sheets, here the expression is working. But the duplicate remark and edit not details is not showing. Please fix my expression.
Valid If formula for column AWB NO (Yes/No,List)
AND(
OR(
NOT(IN([_THIS], MPOP SCAN SHEET[AWB NO])),
[_THISROW_BEFORE].[AWB NO] = [_THISROW_AFTER].[AWB NO]
),
OR(NOT(IN([_THIS], SHEET1[AWB NO])),
[_THISROW_BEFORE].[AWB NO] = [_THISROW_AFTER].[AWB NO]
),
LEN([AWB NO]) > 6,
NOT(
OR(
CONTAINS([_This], "CRP"),
CONTAINS([_This], "~"),
CONTAINS([_This], "&"),
CONTAINS([_This], "_"),
CONTAINS([_This], "MYNR0"),
CONTAINS([_This], "/"),
CONTAINS([_This], "CAMPU")
)))
ALL these statements are true:
....1: ANY of these statements is true:
........1: This statement is false:
............(The value of column 'AWB NO') is one of the values in the list (MPOP SCAN SHEET[AWB NO])
........2: (The value of 'AWB NO' from the row referenced by 'GATE ENTRY NO') is equal to (The value of 'AWB NO' from the row referenced by 'GATE ENTRY NO')
....2: ANY of these statements is true:
........1: This statement is false:
............(The value of column 'AWB NO') is one of the values in the list (Sheet1[AWB NO])
........2: (The value of 'AWB NO' from the row referenced by 'GATE ENTRY NO') is equal to (The value of 'AWB NO' from the row referenced by 'GATE ENTRY NO')
....3: (The text length of (The value of column 'AWB NO')) is greater than (6)
....4: This statement is false:
........ANY of these statements is true:
............1: (The value of column 'AWB NO') contains the text value ("CRP")
............2: (The value of column 'AWB NO') contains the text value ("~")
............3: (The value of column 'AWB NO') contains the text value ("&")
............4: (The value of column 'AWB NO') contains the text value ("_")
............5: (The value of column 'AWB NO') contains the text value ("MYNR0")
............6: (The value of column 'AWB NO') contains the text value ("/")
............7: (The value of column 'AWB NO') contains the text value ("CAMPU")
Error_Message_If_Invalid formula for column AWB NO (Text)
IFS(
AND(
IN([_THIS], MPOP SCAN SHEET[AWB NO]),
[_THISROW_BEFORE].[AWB NO] <> [_THISROW_AFTER].[AWB NO]
)
"Already Scanned This Shipment On " &
SELECT(MPOP SCAN SHEET[Date], [AWB NO] = [_THISROW].[AWB NO],
LEN([AWB NO]) > 6),AND(
IN([_THIS], SHEET1[AWB NO]),
[_THISROW_BEFORE].[AWB NO] <> [_THISROW_AFTER].[AWB NO])
"Already Scanned This Shipment On " &
SELECT(SHEET1[Date], [AWB NO] = [_THISROW].[AWB NO],
LEN([AWB NO]) > 6),
NOT(
OR(
CONTAINS([_This], "CRP"),
CONTAINS([_This], "~"),
CONTAINS([_This], "&"),
CONTAINS([_This], "MYNR0"),
CONTAINS([_This], "_r1"),
CONTAINS([_This], "CAMPU")
)
),
"Invalid Entry, Rescan AWB NO"
)
IFS(
....ALL these statements are true:
........1: (The value of column 'AWB NO') is one of the values in the list (MPOP SCAN SHEET[AWB NO])
........2: (The value of 'AWB NO' from the row referenced by 'GATE ENTRY NO') is not equal to (The value of 'AWB NO' from the row referenced by 'GATE ENTRY NO')
....ALL these statements are true:
........1: (The value of column 'AWB NO') is one of the values in the list (Sheet1[AWB NO])
........2: (The value of 'AWB NO' from the row referenced by 'GATE ENTRY NO') is not equal to (The value of 'AWB NO' from the row referenced by 'GATE ENTRY NO')
....This statement is false:
........ANY of these statements is true:
............1: (The value of column 'AWB NO') contains the text value ("CRP")
............2: (The value of column 'AWB NO') contains the text value ("~")
............3: (The value of column 'AWB NO') contains the text value ("&")
............4: (The value of column 'AWB NO') contains the text value ("MYNR0")
............5: (The value of column 'AWB NO') contains the text value ("_r1")
............6: (The value of column 'AWB NO') contains the text value ("CAMPU")
...."Invalid Entry, Rescan AWB NO")
LinkedIn
Twitter
