Cross sheet referencing

angelapaj

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?

Paul Newcome

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?

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.

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.

Hollie Green

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.

Paul Newcome

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?

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-23T19:11:32+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 :)

Paul Newcome

Happy to help.️