1

SharePoint Guru's please guide me to complete List request.

Requirements: I have been creating a List for doing User Audit

  • Manager has to select the Choice either Approved or Rejected under Access Retain column

  • Whenever Manager selects the Rejected option he/she has to fill the Ticket details on another column (Ticket for Approval) as mandatory but not for Approved choice

  • I am able to achieve the same using this formula: =IF([Access Retain]="Rejected",IF([Ticket for Removal]<>"",TRUE,FALSE),TRUE)

  • Issue comes when we try to the validate the Ticket column which should be 15 char(TKTxxx).

  • I tried LEN Function with different syntax with above formula, none is working as expected.

Improve this question

1 Answer 1

Reset to default
0

Use below formula, it should work for you:

=IF(LEN([Ticket column])=15,IF([Access Retain]="Rejected",IF([Ticket for Removal]<>"",TRUE,FALSE),TRUE),FALSE)

Documentation: Examples of common formulas in lists

Update from comments:

Final working formula is:

=IF([Access Retain]="Rejected",IF(AND([Ticket for Removal]<>"",LEN([Ticket for Removal])=15,LEFT([Ticket for Removal],3)="TKT",TRUE),TRUE,FALSE),TRUE)
Improve this answer
11
  • Thank you Ganesh, Formula is worked as expected but now Approved choice is also asking details on [Ticket for Removal] column, hence tried below formula '=OR(IF(LEN([Ticket for Removal])=0,IF([Access Retain]="Approved",IF([Ticket for Removal]<>"",TRUE,TRUE),FALSE),FALSE ,IF(LEN([Ticket for Removal])=15,IF([Access Retain]="Rejected",IF([Ticket for Removal]<>"",TRUE,FALSE),TRUE),FALSE))' -- Now issue with column is not allowing a Empty field, which is default when we initial ask manger to do audit
    – kirubakaran J
    Commented Aug 18, 2022 at 8:42
  • Is it working for you completely or not? Is [Ticket column] length also dependent on [Access Retain]="Rejected" condition?
    – Ganesh Sanap - MVP
    Commented Aug 18, 2022 at 8:48
  • Yep, Condition is working, but its' also seeking 15 Char for approval choice too.
    – kirubakaran J
    Commented Aug 18, 2022 at 9:31
  • Try using this formula & let me know if it works: =IF([Access Retain]="Rejected",IF(AND([Ticket for Removal]<>"",LEN([Ticket for Removal])=15),TRUE,FALSE),TRUE).
    – Ganesh Sanap - MVP
    Commented Aug 18, 2022 at 9:54
  • 1
    For first two conditions: Ticket for Removal column should not be empty & it should have exactly 15 characters (when [Access Retain] is equal to "Rejected"), this formula should work: =IF([Access Retain]="Rejected",IF(AND([Ticket for Removal]<>"",LEN([Ticket for Removal])=15),TRUE,FALSE),TRUE).
    – Ganesh Sanap - MVP
    Commented Aug 18, 2022 at 11:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.