Spreadsheet: 757886d1638495764-formula-help-example
Sheet: Pick Lists

GEMS NOTIFICATION NUMBER | DOCKET NUMBER | WMS ORDER NUMBER | DATE SUBMITTED | Region | BSA | SERVICE LINE | JOB TYPE | PRIORITY RESPONSE TYPE | SERVICE REQUEST | Raising request Rating | Poor raise  method | Completion  Rating | Poor Completion Reason | Quality of Service Rating | Poor Quality reason | Timeliness Rating | Communication Rating | Poor Communication reason | ADDITIONAL FEEDBACK | Feedback Category | Explanation to Defence: What did we do in response and/or to resolve / rectify | Days to close with customer | [NAME] | Final Classification | OUR NOTES - WHAT's IN WMS. WHO DID WE SPEAK WITH, WHAT DID THEY SAY ETC | STATUS OF ENQUIRIES | WAITING ON | Investigator | ROOT CAUSE | ESCALATION / REPORTING | WO RAISED NAME | WO RAISED POSITION | WORKER / SERVICE DELIVERY PARTNER | WORK SUPERVISOR
 |  |  |  | SNSW | AMB | Estate Upkeep | ARRVM | CST60D |  |  |  |  |  |  |  |  |  |  |  | TBA |  |  | Substantiated | =IFS(AND(Category_Feedback="Mixed", Substantiation="Substantiated"), "Dissatisfaction", (AND(Category_Feedback="Mixed", Substantiation="Not Substantiated")), "Neutral", (AND(Category_Feedback="Mixed", Substantiation="")), "TBA", (Category_Feedback="Complaint"), "Dissatisfaction",  (Category_Feedback="Non-Downer Works"), "Neutral", (Category_Feedback="Compliment"), "Positive",  (Category_Feedback="Positive"), "Positive",(AND(Category_Feedback="", Substantiation="")), "TBA", (Category_Feedback="Negative"), "Dissatisfaction") |  | Closed | Operations Manager | [NAME] | Assigned Incorrectly | N/A
 |  |  |  | QLD | AWMA | Cleaning & Housekeeping | ACRVM | CST5D |  |  |  |  |  |  |  |  |  |  |  | Advice Only |  |  | Not Substantiated |  |  | In Progress | BSSC | [NAME] | Additional Work Requested | Escalate
 |  |  |  |  | BNE | Land Management | ASPLM | EME30M |  |  |  |  |  |  |  |  |  |  |  | Complaint |  |  |  |  |  |  | Investigator |  | [NAME] | Report only
 |  |  |  |  | BPK | Laundry & Dry Cleaning | ASRVM | EME60M |  |  |  |  |  |  |  |  |  |  |  | Compliment |  |  |  |  |  |  | Other |  | Closed Prematurely
 |  |  |  |  | CAN | MIC | CLPLM | IMM24H |  |  |  |  |  |  |  |  |  |  |  | Contact |  |  |  |  |  |  | POC |  | Communication
 |  |  |  |  | CPK | Pest & Vermin | CLRVM | IMM2H |  |  |  |  |  |  |  |  |  |  |  | Mixed |  |  |  |  |  |  | Quality Officer |  | Customer Error
 |  |  |  |  | CRN | Projects & Estate Development | EAORDER | MOD7D |  |  |  |  |  |  |  |  |  |  |  | Negative |  |  |  |  |  |  | Service Delivery Manager |  | Customer Requesting / Providing Information
 |  |  |  |  | CTB | Rescue & Fire Fighting | EUMWA | PLAN1M |  |  |  |  |  |  |  |  |  |  |  | Positive |  |  |  |  |  |  | Works Manager |  | Defence Issue
 |  |  |  |  | DDO | Sports & Recreation | EUPBPLM | PROJ |  |  |  |  |  |  |  |  |  |  |  | Non-Downer  Works |  |  |  |  |  |  |  |  | DLP (Defects Liability Period)
 |  |  |  |  | HAR | Training & Range | EUPBPLMD | RTN14D |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Duplicate WO
 |  |  |  |  | KMA | Transport Services | EUPBRMV | RTN48H |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Exension of Time correctly applied
 |  |  |  |  | RCK | (MSP) Access Control | EUPROJ | URG24H |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Further Investigation Required
 |  |  |  |  | RUS | (MSP) Aircraft Refuelling | EURRVM |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Issue reoccurrence
 |  |  |  |  | SHV | (MSP) Hospitality & Catering | EUSMPLM |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Make safe only
 |  |  |  |  | TWN | (MSP) Petrol Oil & Lubricants (POL) | EUSMPLMD |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Not all items repaired
 |  |  |  |  | TWS | (MSP) Rescue & Fire Fighting | EUSMRMV |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Not completed by Spotless
 |  |  |  |  | WAG | (MSP) Retail Stores | FFPLM |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Outside of Business Rules
 |  |  |  |  |  | (MSP) Waste Management | FFRVM |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Positive
 |  |  |  |  |  |  | [NAME] |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Quality Of Work
 |  |  |  |  |  |  | HKPLM |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Timeliness
 |  |  |  |  |  |  | LDPLM |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Tradeperson incorrect repair
 |  |  |  |  |  |  | [NAME] |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | To be determined
 |  |  |  |  |  |  | LMPLM |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | WO Incorrectly Raised
 |  |  |  |  |  |  | LMPROJ |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Works Bundled
 |  |  |  |  |  |  | LMRVM |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Works completed to required Standard
 |  |  |  |  |  |  | MCASBRVM |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Wrong item repaired
 |  |  |  |  |  |  | MCDLP
 |  |  |  |  |  |  | MCPLM
 |  |  |  |  |  |  | MCRVM
 |  |  |  |  |  |  | NPRVM
 |  |  |  |  |  |  | PDRVM
 |  |  |  |  |  |  | PORVM
 |  |  |  |  |  |  | PVPLM
 |  |  |  |  |  |  | PVRVM
 |  |  |  |  |  |  | [NAME]
 |  |  |  |  |  |  | RPRVM
 |  |  |  |  |  |  | RSRVM
 |  |  |  |  |  |  | [NAME]
 |  |  |  |  |  |  | SCRVM
 |  |  |  |  |  |  | SRPLM
 |  |  |  |  |  |  | SRRVM
 |  |  |  |  |  |  | [NAME]
 |  |  |  |  |  |  | TRRVM
 |  |  |  |  |  |  | TTPLM
 |  |  |  |  |  |  | TTRVM
 |  |  |  |  |  |  | WMRVM