嵌套IF语句和RYGB球
我有一个状态栏，是根据过去的日子手动输入。我正在尝试自动化RYGB球。我已经得到了方程来记录我想要的红色和绿色，但是，我想有一个蓝色的出现，如果状态是在队列中。我遇到的问题是，Days Past Due列返回一个#INVALID DATA TYPE，因为没有审查日期来返回一个数字。
我使用以下公式:
=IF([过期天数]@row> 0，“红色”，IF([逾期天数])@row< 0，“绿色”，IF([过期天数])@row= 0， "Yellow")))
两个问题。
- 是否有一种方法返回一个蓝色的球在RYB球测试列，如果有#无效的数据类型?我试过IFERROR，但一定是做错了什么。
- 如果这不起作用，我如何合并复选框在队列列返回一个蓝色的球，如果检查与方程式的其余部分?
我试了好几种，但都不能做出我想要的凹痕。
评论
-
pieper110621 ✭✭
谢谢你！我没有在公式的前面尝试它。
帮助文章参考资料欧宝体育app官方888
类别
=IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 3, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row) + 1, MONTH([Start Date]@row) - 9, DAY([Start Date]@row)))<\/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":106458,"type":"question","name":"Checking for Duplicates and then Checking for the highest ID Code.","excerpt":"Hi there! We use Smart Sheet for tracking multiple components of our department. On one sheet, I want a formula to check for duplicate names and then look at the ID Code Column and I want a box to be checked for any ID Code that isn't the highest of the duplicates. I have gotten the first part of the formula to work. I…","categoryID":322,"dateInserted":"2023-06-14T19:52:43+00:00","dateUpdated":null,"dateLastComment":"2023-06-15T14:22:20+00:00","insertUserID":162383,"insertUser":{"userID":162383,"name":"jdhubbard09","title":"Logistics Team Lead","url":"https:\/\/community.smartsheet.com\/profile\/jdhubbard09","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-15T14:18:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-15T14:30:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":28,"score":null,"hot":3373613703,"url":"https:\/\/community.smartsheet.com\/discussion\/106458\/checking-for-duplicates-and-then-checking-for-the-highest-id-code","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106458\/checking-for-duplicates-and-then-checking-for-the-highest-id-code","format":"Rich","lastPost":{"discussionID":106458,"commentID":380792,"name":"Re: Checking for Duplicates and then Checking for the highest ID Code.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380792#Comment_380792","dateInserted":"2023-06-15T14:22:20+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-15T14:30:32+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-15T14:18:25+00:00","dateAnswered":"2023-06-15T11:42:38+00:00","acceptedAnswers":[{"commentID":380722,"body":"
You will need a helper column that pulls in the highest id code for the name on each row.<\/p>
=MAX(COLLECT([ID Code]:[ID Code], [Contractor Name]:[Contractor Name], @cell = [Contractor Name]@row))<\/p>
<\/p>
Then the flag formula would look something like this:<\/p>
=IF([ID Code]@row< [Helper Column]@row, 1)<\/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":106457,"type":"question","name":"Formula returning random data","excerpt":"I have a list of 7 courses and with each course comes specific data.The Tuition Type and SHP Item Type numbers are specific to each course. In this screenshot below, I have the courses in a dropdown list and hoping to populate the SHP Items Type and the Tuition Type columns with their corresponding data. It works for many…","categoryID":322,"dateInserted":"2023-06-14T19:52:35+00:00","dateUpdated":null,"dateLastComment":"2023-06-15T11:51:26+00:00","insertUserID":162337,"insertUser":{"userID":162337,"name":"maineL","title":"Admin. & Course Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/maineL","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!plNXqQdBs_o!2kvQ_7y42f4!PTHvB33mICN","dateLastActive":"2023-06-15T14:17:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-15T14:30:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":30,"score":null,"hot":3373604041,"url":"https:\/\/community.smartsheet.com\/discussion\/106457\/formula-returning-random-data","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106457\/formula-returning-random-data","format":"Rich","lastPost":{"discussionID":106457,"commentID":380728,"name":"Re: Formula returning random data","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380728#Comment_380728","dateInserted":"2023-06-15T11:51:26+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-15T14:30:32+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/B81JY03X1O1S\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-15T11:45:10+00:00","dateAnswered":"2023-06-15T11:37:36+00:00","acceptedAnswers":[{"commentID":380721,"body":"
Try specifying an exact match within the MATCH function.<\/p>
=INDEX({Data SHPType}, MATCH(Course@row, {Data Course}, 0<\/strong>))<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":9997,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
