跨多个工作表的状态计数

daisyq
daisyq ✭
在公式和函数

我有几个表在工作空间中跟踪特定的任务。每个表格都有相同的列和格式。我希望能够在仪表板上显示计数，按状态，每个列的状态被跟踪。最好的方法是什么?

标签:
· 在Facebook上分享 在Twitter上分享

最佳答案

答案

帮助文章资源欧宝体育app官方888

在Smartsheet中创建和编辑公式

交叉表引用的公式组合

内容功能列表

类别

=if(Notes@row>=12, Notes@row +2, if(Notes@row <= 9, Notes@row -1, if(Notes@row=0, 0, Notes@row)))<\/p>

This accounts for zeros.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-27 20:06:08","updateUser":{"userID":131616,"name":"Andrea Westrich","url":"https:\/\/community.smartsheet.com\/profile\/Andrea%20Westrich","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!06vS_zj5Rrw!!Vj-bj4lNhSM","dateLastActive":"2022-10-27T20:37:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}}},"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":97070,"type":"question","name":"Need Help with I think an IF Statement","excerpt":"I am trying to generate a date in one column based on an input from a drop down in another column. We have 3 complexity levels Low, Moderate, and High. Low equals 7 days from today, Moderate equals 17 days from today, and High equals 32 days from today. Below is what I have tried and it is not working. =IF((Complexity@row…","categoryID":322,"dateInserted":"2022-10-27T15:17:13+00:00","dateUpdated":null,"dateLastComment":"2022-10-27T15:50:21+00:00","insertUserID":154002,"insertUser":{"userID":154002,"name":"SmartSheet Newbie","url":"https:\/\/community.smartsheet.com\/profile\/SmartSheet%20Newbie","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/ALm5wu1VcnmvyGv6lWBPJmcBlo2-f4c9qfr0HlrcGUdo=s96-c","dateLastActive":"2022-10-27T15:48:43+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":154002,"lastUser":{"userID":154002,"name":"SmartSheet Newbie","url":"https:\/\/community.smartsheet.com\/profile\/SmartSheet%20Newbie","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/ALm5wu1VcnmvyGv6lWBPJmcBlo2-f4c9qfr0HlrcGUdo=s96-c","dateLastActive":"2022-10-27T15:48:43+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":21,"score":null,"hot":3333770854,"url":"https:\/\/community.smartsheet.com\/discussion\/97070\/need-help-with-i-think-an-if-statement","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/97070\/need-help-with-i-think-an-if-statement","format":"Rich","lastPost":{"discussionID":97070,"commentID":349001,"name":"Re: Need Help with I think an IF Statement","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/349001#Comment_349001","dateInserted":"2022-10-27T15:50:21+00:00","insertUserID":154002,"insertUser":{"userID":154002,"name":"SmartSheet Newbie","url":"https:\/\/community.smartsheet.com\/profile\/SmartSheet%20Newbie","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/ALm5wu1VcnmvyGv6lWBPJmcBlo2-f4c9qfr0HlrcGUdo=s96-c","dateLastActive":"2022-10-27T15:48:43+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\/ML695KMJUM5Q\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2022-10-27T15:49:34+00:00","dateAnswered":"2022-10-27T15:47:35+00:00","acceptedAnswers":[{"commentID":349000,"body":"

Hello,<\/p>

It appears to be an issue of parentheses. You need to embed your next IF formula as the 'value_if_false' of the IF statement before it.<\/p>

Use this one if you want to default to Moderate when the Complexity Cell is Blank:<\/em> =IF((Complexity@row = \"High\", TODAY(32)), (IF(Complexity@row = \"LOW\", TODAY(7))), (IF(Complexity@row = \"Moderate\", TODAY(17), TODAY(17))))<\/p>

Use this one if you want to instruct people to enter the Complexity when it is left blank:<\/em> =IF(Complexity@row = \"High\", TODAY(32), IF(Complexity@row = \"Low\", TODAY(7), IF(Complexity@row = \"Moderate\", TODAY(17), \"Enter Complexity\")))<\/p>

I hope that that help!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-27 15:49:34","updateUser":{"userID":154002,"name":"SmartSheet Newbie","url":"https:\/\/community.smartsheet.com\/profile\/SmartSheet%20Newbie","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/ALm5wu1VcnmvyGv6lWBPJmcBlo2-f4c9qfr0HlrcGUdo=s96-c","dateLastActive":"2022-10-27T15:48:43+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":97058,"type":"question","name":"Aggregate Red, Yellow, Green Status to One Overall Grade","excerpt":"Good day, I am looking to Aggregate Red\/Yellow\/Green status into one overall R\/Y\/G. Here is the criteria for grade 1: All classes are either yellow, or green, overall grade = green 3 classes at yellow, overall grade = yellow Any one class at red, overall grade = red Here is what I have tried, =IF(AND([Grade]@row = \"1\",…","categoryID":322,"dateInserted":"2022-10-27T11:44:27+00:00","dateUpdated":"2022-10-27T12:10:57+00:00","dateLastComment":"2022-10-27T15:22:14+00:00","insertUserID":7607,"insertUser":{"userID":7607,"name":"Marvin Francis","url":"https:\/\/community.smartsheet.com\/profile\/Marvin%20Francis","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-27T15:20:41+00:00","banned":0,"punished":0,"private":false,"label":""},"updateUserID":91566,"lastUserID":7607,"lastUser":{"userID":7607,"name":"Marvin Francis","url":"https:\/\/community.smartsheet.com\/profile\/Marvin%20Francis","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-27T15:20:41+00:00","banned":0,"punished":0,"private":false,"label":""},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":31,"score":null,"hot":3333760001,"url":"https:\/\/community.smartsheet.com\/discussion\/97058\/aggregate-red-yellow-green-status-to-one-overall-grade","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/97058\/aggregate-red-yellow-green-status-to-one-overall-grade","format":"Rich","lastPost":{"discussionID":97058,"commentID":348994,"name":"Re: Aggregate Red, Yellow, Green Status to One Overall Grade","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/348994#Comment_348994","dateInserted":"2022-10-27T15:22:14+00:00","insertUserID":7607,"insertUser":{"userID":7607,"name":"Marvin Francis","url":"https:\/\/community.smartsheet.com\/profile\/Marvin%20Francis","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-27T15:20:41+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\/DGJEWDUDRANK\/20221027-smartsheet-example.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"20221027 Smartsheet Example.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2022-10-27T13:53:10+00:00","dateAnswered":"2022-10-27T13:21:11+00:00","acceptedAnswers":[{"commentID":348928,"body":"

@Marvin Francis<\/a> <\/p>

Hi Marvin, you were very close! Since your red requirement overrides all others, you can put it first. Then check for any greens. If not either of those, it's yellow...<\/p>

=IF(OR([Class1]@row = \"Red\", [Class2]@row = \"Red\", [Class3]@row = \"Red\"), \"Red\", IF(OR([Class1]@row = \"Green\", [Class2]@row = \"Green\", [Class3]@row = \"Green\"), \"Green\", \"Yellow\"))<\/p>

Please let me know if that works for you!<\/p>

-Ryan<\/p>"},{"commentID":348992,"body":"

@Marvin Francis<\/a> Ah ok. <\/p>

Yes, wrap it in an if statement first.<\/p>

=IF(Grade@row = \"1\", IF(OR([Class1]@row = \"Red\", [Class2]@row = \"Red\", [Class3]@row = \"Red\"), \"Red\", IF(OR([Class1]@row = \"Green\", [Class2]@row = \"Green\", [Class3]@row = \"Green\"), \"Green\", \"Yellow\")), \"//www.santa-greenland.com/community/discussion/90692/\")<\/p>

Then you could add a portion for Grade 2:<\/p>

=IF(Grade@row = \"1\", IF(OR([Class1]@row = \"Red\", [Class2]@row = \"Red\", [Class3]@row = \"Red\"), \"Red\", IF(OR([Class1]@row = \"Green\", [Class2]@row = \"Green\", [Class3]@row = \"Green\"), \"Green\", \"Yellow\")), IF(Grade@row = \"2\", do what you need to do here))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-27 13:53:10","updateUser":{"userID":7607,"name":"Marvin Francis","url":"https:\/\/community.smartsheet.com\/profile\/Marvin%20Francis","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-27T15:20:41+00:00","banned":0,"punished":0,"private":false,"label":""}}},"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":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">