I need a formula that when a job is booked in, will check to see if team members assigned to it have liability insurance. I have a separate sheet (Certificates v2.0) with our workers listed and whether their liability is Current or Expired. The following formula works but only when there is a single team member in the Team column. Obviously when there are two contacts in the cell it no longer matches the list in the Certificates sheet, even with 'True' used in my VLOOKUP. Can anyone help please?
Hi@Paul Newcome, thanks for the speedy response but I don't think this will work. I need the formula to return either Current or Expired which then triggers conditional formatting to highlight if someone on the team's insurance has run out.
Are you able to provide some screenshots for context?
You could still use a COUNTIFS where you count (among the rest of the criteria) how many rows also contain "Current". If that count is at least one, then output "Current", but if the count is equal to zero then "Expired".
This first screenshot shows the areas of the main sheet that I'm using. If theStatusis Booked In or WIP, check theTeamand return Current or Expired. The conditional formatting is based on the returned value inSite Manager Liability. In this example Bailey is showing correctly as Expired, however Kev & Pete both have their Public Liability insurance so should be showing as Current.
This screenshot shows where I track the teams' certificates and insurances. Bailey is new so has nothing! But Kev and Pete both have 'in date'Public Liability.
Do you have a maximum number of team members assigned to a project?
Are you trying to identify if all team members have liability insurance?
All the mulit selection cells split the items by a return ... Char(10) so you can split them out using a series of REPLACE() and FIND(). The more potential options you have the more complicated the formula gets. If it's a small number of team members, I would created helper columns that split them out and then check that each one has the insurance.
There would generally only be one or two team members assigned to a project, and if there are any more they're normally 'external' and we wouldn't be responsible for their liability.
As for your other question, I'd need to know if either of the two team members' insurance had lapsed.
@Paul NewcomeI didn't realize the HAS function worked that way, very cool. Do you know if it defaults to Has Any vs. Has All? And is there a way to toggle between them?
Try IF([payment voucher]@row=0,Sum([Parking Revenue Regular]@row:[Private boat parking revenue]@row),\"//www.santa-greenland.com/community/discussion/comment/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106901,"type":"question","name":"Can you use a COUNTIF formula to county symbols?","excerpt":"I have a list of goals for the year and we are using harvey ball symbols to indicate the percent complete an the red, yellow, green, gray balls to indicate the health\/status of the project. I would like to display a report on my dashboard that shows the number of projects we have in each harvey ball stage and also in each…","categoryID":322,"dateInserted":"2023-06-26T15:59:48+00:00","dateUpdated":"2023-06-26T16:00:12+00:00","dateLastComment":"2023-06-26T16:29:45+00:00","insertUserID":162764,"insertUser":{"userID":162764,"name":"Stephanie D","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Stephanie%20D","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mctWfgWrL7o!n-DX3ymVfmQ!msa32mT4_k9","dateLastActive":"2023-06-26T16:52:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":162764,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-26T18:56:03+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":24,"score":null,"hot":3375593973,"url":"https:\/\/community.smartsheet.com\/discussion\/106901\/can-you-use-a-countif-formula-to-county-symbols","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106901\/can-you-use-a-countif-formula-to-county-symbols","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106901,"commentID":382346,"name":"Re: Can you use a COUNTIF formula to county symbols?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382346#Comment_382346","dateInserted":"2023-06-26T16:29:45+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-26T18:56:03+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-26T16:24:48+00:00","dateAnswered":"2023-06-26T16:08:42+00:00","acceptedAnswers":[{"commentID":382328,"body":"
Paul Newcome ✭✭✭✭✭✭
sharkasits ✭✭✭✭
