Cross sheet referencing
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?
Best 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?
Answers
-
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.
-
@Hollie Green thank you, it was a good shout - unfortunately it didn't work. For some reasons it's giving me 0s in each row.
-
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.
-
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?
-
thank you! it worked :)
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!