Cross sheet referencing

angelapaj
angelapaj ✭✭✭
inFormulas and Functions

Hi All,

I hope you're well.

I'm wondering if you can help me with my issue.

We have a RAID log for all the issues/risks on our projects. On the log, they are being identified by job numbers. We also have our main master tracker with all the projects on it. On the master tracker, there also is a column named job numbers - using same numbers as the RAID log.

Is there any way of creating a link/using a formula to ask smartsheet to highlight the cell in a certain colour on the master tracker if the same job number appears on the RAID log so by looking at the master tracker we can straightaway see which jobs are at risk?

· Share on Facebook Share on Twitter

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    There are sometimes issues with data containing leading zeros. Try an @cell reference like so:

    =COUNTIFS({Range},@cell =[Job Number]@row)


    Do you have any job numbers anywhere that do not have a leading zero?

    thinkspi.com

    · Share on Facebook Share on Twitter

Answers

  • Hollie Green
    Hollie Green ✭✭✭✭✭

    You would need to create a helper column and then you could set up your conditional formatting.

    =Countif({Raid log # reference},[Job numbers]@row)

    Then you can set your conditional formatting for if that column is greater than 0 to highlight in whatever color you want.

    · Share on Facebook Share on Twitter
  • angelapaj
    angelapaj ✭✭✭

    @Hollie Greenthank you, it was a good shout - unfortunately it didn't work. For some reasons it's giving me 0s in each row.

    image.png
    image.png


    · Share on Facebook Share on Twitter
  • Hollie Green
    Hollie Green ✭✭✭✭✭
    edited 06/23/23

    check your data and make sure there are no extra spaces in either sheet. They have to match exactly for the formula to work. Also the 0's at the front their has to be the same number of them.

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    There are sometimes issues with data containing leading zeros. Try an @cell reference like so:

    =COUNTIFS({Range},@cell =[Job Number]@row)


    Do you have any job numbers anywhere that do not have a leading zero?

    thinkspi.com

    · Share on Facebook Share on Twitter
  • angelapaj
    angelapaj ✭✭✭
    There are sometimes issues with data containing leading zeros. Try an @cell reference like so:<\/p>

    =COUNTIFS({Range}, @cell =<\/strong> [Job Number]@row)<\/p>

    Do you have any job numbers anywhere that do not have a leading zero?<\/p>","bodyRaw":"[{\"insert\":\"There are sometimes issues with data containing leading zeros. Try an @cell reference like so:\\n=COUNTIFS({Range}, \"},{\"attributes\":{\"bold\":true},\"insert\":\"@cell =\"},{\"insert\":\" [Job Number]@row)\\n\\nDo you have any job numbers anywhere that do not have a leading zero?\\n\"}]","format":"rich","dateInserted":"2023-06-23T14:03:08+00:00","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-29T12:07:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381965#Comment_381965","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/381965#Comment_381965

    thank you! it worked :)

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help.️

    thinkspi.com

    · Share on Facebook Share on Twitter

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公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
Give this a try:<\/p>

=IF([CRM Portfolio]@row = 1, IF(Jurisdiction@row = \"Federal\", \"Jason\", VLOOKUP(State@row, {CL_States Range 2}, 2, false)))<\/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":107038,"type":"question","name":"Modified Date loses detail when referenced","excerpt":"Application: Trying to capture and display a 'sheet last modified' value on a dashboard. Approach: Using a formula in the sheet summary sidebar to find the max value of all timestamps in the 'Modified' column. Formula is as follows and is functioning as expected. =MAX([Modified]:[Modified]) Problem: The displayed value…","snippet":"Application: Trying to capture and display a 'sheet last modified' value on a dashboard. Approach: Using a formula in the sheet summary sidebar to find the max value of all…","categoryID":322,"dateInserted":"2023-06-28T17:43:23+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T12:02:41+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-28T21:44:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-29T12:09:52+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":28,"score":null,"hot":3376016164,"url":"https:\/\/community.smartsheet.com\/discussion\/107038\/modified-date-loses-detail-when-referenced","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107038\/modified-date-loses-detail-when-referenced","format":"Rich","lastPost":{"discussionID":107038,"commentID":383033,"name":"Re: Modified Date loses detail when referenced","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383033#Comment_383033","dateInserted":"2023-06-29T12:02:41+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-29T12:09:52+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-28T21:29:15+00:00","dateAnswered":"2023-06-28T18:46:15+00:00","acceptedAnswers":[{"commentID":382932,"body":"

Set the Sheet Summary field as text\/number then add +\"//www.santa-greenland.com/community/discussion/comment/\" to the end of the MAX function (plus quote quote) to convert it into a text string.<\/p>

=MAX([Modified]:[Modified]) + \"//www.santa-greenland.com/community/discussion/comment/\"<\/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":107021,"type":"question","name":"Formula for %","excerpt":"Im looking for a formula to return the percentage that our projects are over\/under.","snippet":"Im looking for a formula to return the percentage that our projects are over\/under.","categoryID":322,"dateInserted":"2023-06-28T15:05:06+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T02:08:33+00:00","insertUserID":161805,"insertUser":{"userID":161805,"name":"TPALJA","title":"Scheduler","url":"https:\/\/community.smartsheet.com\/profile\/TPALJA","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!2UcnvvuysSo!!7FqQoFuT_Rw","dateLastActive":"2023-06-29T02:06:47+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":161805,"lastUser":{"userID":161805,"name":"TPALJA","title":"Scheduler","url":"https:\/\/community.smartsheet.com\/profile\/TPALJA","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!2UcnvvuysSo!!7FqQoFuT_Rw","dateLastActive":"2023-06-29T02:06:47+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":31,"score":null,"hot":3375970419,"url":"https:\/\/community.smartsheet.com\/discussion\/107021\/formula-for","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107021\/formula-for","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107021,"commentID":382987,"name":"Re: Formula for %","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382987#Comment_382987","dateInserted":"2023-06-29T02:08:33+00:00","insertUserID":161805,"insertUser":{"userID":161805,"name":"TPALJA","title":"Scheduler","url":"https:\/\/community.smartsheet.com\/profile\/TPALJA","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!2UcnvvuysSo!!7FqQoFuT_Rw","dateLastActive":"2023-06-29T02:06:47+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\/O3NPEPI0XAMQ\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-29T02:07:09+00:00","dateAnswered":"2023-06-28T15:32:28+00:00","acceptedAnswers":[{"commentID":382861,"body":"

If you want to know the percentage over\/under the Contract Amount<\/strong>, your formula (placed the [Percentage] column) would be:<\/p>

=([Contract amount]@row - [Install Labor (actual)]@row) \/ [Contract amount]@row<\/p>

Be sure the \"Percentage\" column is formatted as a percentage. Positive numbers show that your total spend is under<\/strong> the [Contract amount]. Negative values show your total spend is over<\/strong>.<\/p>

You can use a similar formula to measure how far over\/under your [Labor $ (quoted)] amount is from your [Install Labor (actual)] amount.<\/p>

=([Labor $ (quoted)]@row - [Install Labor (actual)]@row) \/ [Labor $ (quoted)]@row<\/p>

Here, though, a negative value shows that you are OVER<\/strong> the estimate. A positive value shows you are at or UNDER<\/strong> the estimate.<\/p>

\n
\n \n \"Screenshot<\/img><\/a>\n <\/div>\n<\/div>\n


<\/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

  • IF AND/OR plus a VLOOKUP

    I'm having trouble getting a formula correct; I keep getting unparsable or circular reference. I'm wanting to add the name of our rep based on the status of two columns. If the CRM Portfolio is checked AND the Jurisdiction = Federal, I want to insert the name "Jason". If the CRM Portfolio is checked and the Jurisdiction is…

  • Modified Date loses detail when referenced

    Application: Trying to capture and display a 'sheet last modified' value on a dashboard. Approach: Using a formula in the sheet summary sidebar to find the max value of all timestamps in the 'Modified' column. Formula is as follows and is functioning as expected. =MAX([Modified]:[Modified]) Problem: The displayed value…

  • Formula for %

    Im looking for a formula to return the percentage that our projects are over/under.