% Complete Report using boxes specific to create report

imtonydillard
imtonydillard ✭
inFormulas and Functions
question for ss board.png

I use the Board Report Column as a filter only. More specifically, it allows me to filter only the items they want to see on their report. In addition, I need to provide them with a "% Complete" of only those items using based on the following: Not Applicable needs to be excluded, Not Started needs to be 0% and Complete needs to be 100%.

How do I do this

Tags:
· Share on Facebook Share on Twitter

Best Answer

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Hi<\/p>

I believe this formula, entered in the TITLE (Formula) column would work for you:<\/p>

=IF(ISBLANK([INT \/ DOM \/ TEMP \/ NG]@row), \"//www.santa-greenland.com/community/discussion/comment/\", TITLE$1)<\/p>


<\/p>

It creates this:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>

You start with an IF<\/p>

For the logical expression you use ISBLANK<\/p>

ISBLANK([INT \/ DOM \/ TEMP \/ NG]@row) is checking that the cell in the column INT \/ DOM \/ TEMP \/ NG is blank<\/p>

If that is true it puts in \"//www.santa-greenland.com/community/discussion/comment/\" (ie nothing)<\/p>

If it is false it puts in the value in the TITLE column, but instead of TITLE@row, it pulls just from row 1 because of the $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":112287,"type":"question","name":"Count two columns of a sheet that each match specific criteria","excerpt":"Hello, I am trying to create a count that both matches one of 4 specific project initiatives and shows approved in another column. Example: Lead by Example (One Column) and the Status is \"Approved\" in another column.","snippet":"Hello, I am trying to create a count that both matches one of 4 specific project initiatives and shows approved in another column. Example: Lead by Example (One Column) and the…","categoryID":322,"dateInserted":"2023-10-27T18:08:34+00:00","dateUpdated":null,"dateLastComment":"2023-10-27T20:42:18+00:00","insertUserID":169195,"insertUser":{"userID":169195,"name":"Bob Schweighart","url":"https:\/\/community.smartsheet.com\/profile\/Bob%20Schweighart","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:41:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":169195,"lastUser":{"userID":169195,"name":"Bob Schweighart","url":"https:\/\/community.smartsheet.com\/profile\/Bob%20Schweighart","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:41:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":22,"score":null,"hot":3396870652,"url":"https:\/\/community.smartsheet.com\/discussion\/112287\/count-two-columns-of-a-sheet-that-each-match-specific-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112287\/count-two-columns-of-a-sheet-that-each-match-specific-criteria","format":"Rich","lastPost":{"discussionID":112287,"commentID":402120,"name":"Re: Count two columns of a sheet that each match specific criteria","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402120#Comment_402120","dateInserted":"2023-10-27T20:42:18+00:00","insertUserID":169195,"insertUser":{"userID":169195,"name":"Bob Schweighart","url":"https:\/\/community.smartsheet.com\/profile\/Bob%20Schweighart","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:41:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-27T21:43:37+00:00","dateAnswered":"2023-10-27T20:13:32+00:00","acceptedAnswers":[{"commentID":402113,"body":"

@Bob Schweighart<\/a> you just need the COUNTIFS formula and put those 2 criterion in it. Are they distinct counts for the 4 different projects? If so, that is easier. If you are counting all 4, then you need to decide on an imbedded OR or a NOT.<\/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":112242,"type":"question","name":"Can I use Index\/Match and Left functions in one formula?","excerpt":"Hello, can someone help me with the left function within the index\/match? This is what I tried but it always returns NO MATCH: =INDEX({Master Job SO}, MATCH(LEFT([Job Number]@row,5), {Master Job #}, 0)) I also tried creating a helper column, Job Prefix, using =LEFT([Job Number]@row, 5) and using that result in the SO…","snippet":"Hello, can someone help me with the left function within the index\/match? This is what I tried but it always returns NO MATCH: =INDEX({Master Job SO}, MATCH(LEFT([Job…","categoryID":322,"dateInserted":"2023-10-26T22:39:07+00:00","dateUpdated":null,"dateLastComment":"2023-10-27T17:46:03+00:00","insertUserID":157316,"insertUser":{"userID":157316,"name":"amber.lange","url":"https:\/\/community.smartsheet.com\/profile\/amber.lange","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:44:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":157316,"lastUser":{"userID":157316,"name":"amber.lange","url":"https:\/\/community.smartsheet.com\/profile\/amber.lange","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:44:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":33,"score":null,"hot":3396791110,"url":"https:\/\/community.smartsheet.com\/discussion\/112242\/can-i-use-index-match-and-left-functions-in-one-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112242\/can-i-use-index-match-and-left-functions-in-one-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":112242,"commentID":402089,"name":"Re: Can I use Index\/Match and Left functions in one formula?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402089#Comment_402089","dateInserted":"2023-10-27T17:46:03+00:00","insertUserID":157316,"insertUser":{"userID":157316,"name":"amber.lange","url":"https:\/\/community.smartsheet.com\/profile\/amber.lange","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:44:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"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\/RTC0V6MZN24K\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-27T17:46:07+00:00","dateAnswered":"2023-10-27T17:30:20+00:00","acceptedAnswers":[{"commentID":402081,"body":"

There we go. The [SO] column looks like it is being manually populated which is storing the data as numerical data, but the LEFT function outputs text.<\/p>


<\/p>

Insert a column on the reference sheet and use<\/p>

=SO@row + \"//www.santa-greenland.com/community/discussion/comment/\"<\/p>

plus quote quote<\/p>


<\/p>

This will convert all entries into a text string so that data types match. You would then match on this helper column in your INDEX\/MATCH.<\/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":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]}],"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":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • Formula help! If cell is not blank, return value from another cell

    I'm having trouble building a formula that works for the below requirement (see screenshot for reference). If the cell in column 'INT / DOM / TEMP / NG' is NOT BLANK, then I need the column 'TITLE (Formula)' to pull through the value from a specific cell ('A1' in Excel terms, or 'TITLE / Security Title' in the example…

  • Count two columns of a sheet that each match specific criteria

    Hello, I am trying to create a count that both matches one of 4 specific project initiatives and shows approved in another column. Example: Lead by Example (One Column) and the Status is "Approved" in another column.

  • Can I use Index/Match and Left functions in one formula?

    Hello, can someone help me with the left function within the index/match? This is what I tried but it always returns NO MATCH: =INDEX({Master Job SO}, MATCH(LEFT([Job Number]@row,5), {Master Job #}, 0)) I also tried creating a helper column, Job Prefix, using =LEFT([Job Number]@row, 5) and using that result in the SO…