DISPLAY BOTTOM ENTRY ON A COLUMN

How can I display the bottom (last entered) value of a column in a different "cell"

Capture.JPG

Tags:

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 04/29/19

    Hi John,

    Here's an example using an INDEX function.

    +1 is because it seems like you have one row empty in the top.

    Depending on what you want to get the bottom value of, you could also use a MAX function. =MAX(Value:Value)

    Try something like this.

    =INDEX(Value:Value; COUNT((Value:Value)) + 1)

    The same version but with the below changes for your and others convenience.

    =INDEX(Value:Value, COUNT((Value:Value)) + 1)

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
DATE(2023, 6, 1), [Date Closed]:[Date Closed], DATE(2023, 6, 1), [Date…","categoryID":322,"dateInserted":"2023-08-01T14:29:49+00:00","dateUpdated":null,"dateLastComment":"2023-08-01T19:48:18+00:00","insertUserID":155497,"insertUser":{"userID":155497,"name":"JSpears","url":"https:\/\/community.smartsheet.com\/profile\/JSpears","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BgDcwSo-92c!7zlc-6IqQLs!HEmr4KUqqxd","dateLastActive":"2023-08-01T20:31:54+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-08-01T20:06:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":33,"score":null,"hot":3381821287,"url":"https:\/\/community.smartsheet.com\/discussion\/108345\/averageif-with-multiple-criterion","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108345\/averageif-with-multiple-criterion","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108345,"commentID":388300,"name":"Re: AverageIf with multiple criterion","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388300#Comment_388300","dateInserted":"2023-08-01T19:48:18+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-08-01T20:06:15+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\/EFAIAZ6PP2LD\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-01T15:53:35+00:00","dateAnswered":"2023-08-01T14:41:23+00:00","acceptedAnswers":[{"commentID":388203,"body":"

Try this:<\/p>

=AVG(COLLECT([Resolution Time (Days)]:[Resolution Time (Days)], [Date Closed]:[Date Closed], AND(@cell>DATE(2023, 6, 1), @cell<DATE(2023, 6, 30))))<\/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"}]},{"discussionID":108361,"type":"question","name":"COUNTIFS+CONTAINS+AND","excerpt":"I am trying to write a sheet summary formula to count the number of rows where: Sprint Name contains \"Maintenance\", Date Resolved is not blank, and Bug or Request equals \"Bug\" [Sprint Name]:[Sprint Name], CONTAINS(\"Maintenance\") and [Date Resolved]:[Date Resolved], <>\"\" and [Bug or Request]:[Bug or Request], \"Bug\" I tried…","snippet":"I am trying to write a sheet summary formula to count the number of rows where: Sprint Name contains \"Maintenance\", Date Resolved is not blank, and Bug or Request equals \"Bug\"…","categoryID":322,"dateInserted":"2023-08-01T16:49:25+00:00","dateUpdated":null,"dateLastComment":"2023-08-01T17:13:01+00:00","insertUserID":139576,"insertUser":{"userID":139576,"name":"K Miller","url":"https:\/\/community.smartsheet.com\/profile\/K%20Miller","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hAtpoYJ45_M!Dl1TQ0DO0iQ!MPp8IQrC3KR","dateLastActive":"2023-08-01T17:51:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":139576,"lastUser":{"userID":139576,"name":"K Miller","url":"https:\/\/community.smartsheet.com\/profile\/K%20Miller","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hAtpoYJ45_M!Dl1TQ0DO0iQ!MPp8IQrC3KR","dateLastActive":"2023-08-01T17:51:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":35,"score":null,"hot":3381819746,"url":"https:\/\/community.smartsheet.com\/discussion\/108361\/countifs-contains-and","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108361\/countifs-contains-and","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108361,"commentID":388255,"name":"Re: COUNTIFS+CONTAINS+AND","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388255#Comment_388255","dateInserted":"2023-08-01T17:13:01+00:00","insertUserID":139576,"insertUser":{"userID":139576,"name":"K Miller","url":"https:\/\/community.smartsheet.com\/profile\/K%20Miller","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hAtpoYJ45_M!Dl1TQ0DO0iQ!MPp8IQrC3KR","dateLastActive":"2023-08-01T17:51:43+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-08-01T17:51:41+00:00","dateAnswered":"2023-08-01T17:08:09+00:00","acceptedAnswers":[{"commentID":388251,"body":"

CONTAINS() must include the range to compare. You can use @cell in this instance.<\/p>

=COUNTIFS([Sprint Name]:[Sprint Name], CONTAINS(\"Maintenance\", @cell), [Date Resolved]:[Date Resolved], <>\"\", [Bug or Request]:[Bug or Request], \"Request\")<\/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"}]},{"discussionID":108339,"type":"question","name":"Help on IF formula... validating two columns.","excerpt":"Hi, hope everyone is doing well? hoping someone could please help me with this formula, at the moment I am using the below formula to check the % from Score column and give the outcome result in the Result column and this is working fine, just wondering, if there a way I can include ‘Auto Fail’ in the above formula so if…","snippet":"Hi, hope everyone is doing well? hoping someone could please help me with this formula, at the moment I am using the below formula to check the % from Score column and give the…","categoryID":322,"dateInserted":"2023-08-01T11:55:34+00:00","dateUpdated":null,"dateLastComment":"2023-08-01T13:58:19+00:00","insertUserID":159253,"insertUser":{"userID":159253,"name":"ovelzone","url":"https:\/\/community.smartsheet.com\/profile\/ovelzone","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-01T16:27:58+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-08-01T20:06:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":35,"score":null,"hot":3381791633,"url":"https:\/\/community.smartsheet.com\/discussion\/108339\/help-on-if-formula-validating-two-columns","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108339\/help-on-if-formula-validating-two-columns","format":"Rich","lastPost":{"discussionID":108339,"commentID":388190,"name":"Re: Help on IF formula... validating two columns.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388190#Comment_388190","dateInserted":"2023-08-01T13:58:19+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-08-01T20:06:15+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\/C0TNHS5393OE\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-01T13:41:25+00:00","dateAnswered":"2023-08-01T12:08:16+00:00","acceptedAnswers":[{"commentID":388167,"body":"

Try this one:<\/p>

=IF([Autofail]@row = \"Y\", \"Auto Fail\", IF([Score]@row < 0.5, \"Fail\", IF(AND([Score]@row > 0.5, [Score]@row < 0.65), \"Pass\", IF(AND([Score]@row > 0.64, [Score]@row < 0.75), \"Credit\", IF(AND([Score]@row > 0.74, Score]@row < 0.85), \"Distinction\", IF(AND([Score]@row > 0.84, [Score]@row <= 1), \"High Distinction\"))))))<\/p>"},{"commentID":388172,"body":"

This should work for you:<\/p>

=IF(Autofail@row = \"Y\", \"Auto Fail\", IF(Score@row< 0.5, \"Fail\", IF(Score@row< 0.65, \"Pass\", IF(Score@row< 0.75, \"Credit\", IF(Score@row<0.85, \"Distinction\", \"High Distinction\")))))<\/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":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions