具有多个联系人的VLOOKUP
最佳答案
答案
-
保罗新来的 ✭✭✭✭✭✭ -
保罗新来的 ✭✭✭✭✭✭
你能提供一些屏幕截图吗?
您仍然可以使用COUNTIFS来计算(在其他条件中)有多少行也包含“Current”。如果该计数至少为1，则输出“Current”，但如果计数等于零，则输出“Expired”。
thinkspi.com
-
给 ✭✭✭✭✭✭
第一个截图显示了我正在使用的主表单的区域。如果状态是在制品还是在制品，请检查团队并返回Current或Expired。中的返回值为条件格式现场经理责任．在这个例子中，Bailey正确地显示为过期，但是Kev和Pete都有他们的公共责任保险，所以应该显示为当前。
这个截图显示了我在哪里跟踪团队的证书和保险。贝利是新来的，所以什么都没有!但是Kev和Pete都有“约会”公众责任．
-
sharkasits ✭✭✭✭@Kristine多重联系人会变得复杂。
- 您是否为一个项目分配了最大数量的团队成员?
- 您是否试图确定是否所有团队成员都有责任保险?
所有的多选择单元格通过返回分隔项目…Char(10)，以便您可以使用一系列REPLACE()和FIND()将它们分开。可能的选项越多，公式就越复杂。如果只有少数团队成员，我会创建助手列，将他们分开，然后检查每个人是否都有保险。
-
给 ✭✭✭✭✭✭
谢谢@sharkasits好主意!
一般来说，一个项目只会分配一到两个团队成员，如果有更多的成员，他们通常是“外部的”，我们不会对他们的责任负责。
关于你的另一个问题，我想知道两名队员的保险是否已经失效。
我会试试的，谢谢你!
-
sharkasits ✭✭✭✭
@Kristine听起来不错。如果你遇到任何问题，请告诉我。
-
保罗新来的 ✭✭✭✭✭✭
-
sharkasits ✭✭✭✭
@Paul新来的我没有意识到HAS功能是这样工作的，非常酷。你知道它默认是Has Any还是Has All吗?有办法在它们之间切换吗?
-
保罗新来的 ✭✭✭✭✭✭
帮助文章参考资料欧宝体育app官方888
If you have a unique ID (like ticket number) that is in both sheets, you could use DataMesh if you have access to that or cross sheet formulas to pull in the data you want based on the matching unique ID.<\/p>
Otherwise, you'd have to use an Add-on. If you are interested in hearing about an add-on that can do this, send me a message at https:\/\/smartsheetguru.com\/contact<\/a><\/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":106845,"type":"question","name":"Count function","excerpt":"Hi I need help with a formula. I have a column that I want to count but only based on a values in a different column. I have tried the following formula but it's not working and I can't figure out what I am doing wrong. =countif([Test Script #]:[Test Script #], [Pass ?]:[Pass ?], <> \"N\/A\") The Test Script # column is the…","categoryID":322,"dateInserted":"2023-06-23T17:50:06+00:00","dateUpdated":null,"dateLastComment":"2023-06-23T19:30:43+00:00","insertUserID":127983,"insertUser":{"userID":127983,"name":"Carol-Anne Cerbone","url":"https:\/\/community.smartsheet.com\/profile\/Carol-Anne%20Cerbone","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!h1jY4Qxc4TY!prNYaQRZvvo!a6wiwaB2GsY","dateLastActive":"2023-06-23T19:29:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":127983,"lastUser":{"userID":127983,"name":"Carol-Anne Cerbone","url":"https:\/\/community.smartsheet.com\/profile\/Carol-Anne%20Cerbone","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!h1jY4Qxc4TY!prNYaQRZvvo!a6wiwaB2GsY","dateLastActive":"2023-06-23T19:29:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":47,"score":null,"hot":3375096049,"url":"https:\/\/community.smartsheet.com\/discussion\/106845\/count-function","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106845\/count-function","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106845,"commentID":382093,"name":"Re: Count function","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382093#Comment_382093","dateInserted":"2023-06-23T19:30:43+00:00","insertUserID":127983,"insertUser":{"userID":127983,"name":"Carol-Anne Cerbone","url":"https:\/\/community.smartsheet.com\/profile\/Carol-Anne%20Cerbone","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!h1jY4Qxc4TY!prNYaQRZvvo!a6wiwaB2GsY","dateLastActive":"2023-06-23T19:29:42+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-23T19:30:46+00:00","dateAnswered":"2023-06-23T19:28:59+00:00","acceptedAnswers":[{"commentID":382092,"body":"
