Conditional Formatting Across Sheets

Emmet McKenna
Emmet McKenna ✭✭
inSmartsheet Basics

Hi, we have a Scheduling Sheet (with columns, wk 01, wk 02, wk 03, wk 04, etc). A Delivery Date column links into this sheet from another sheet.

What we do at present is highlight the relevant week column based on the date in delivery date, so for example from Jan 2nd to Jan 8th is wk 01 this year. (See screenshot attached)

Scheduling Sheet.jpg

The problem is the Delivery Dates can often change so the highlighted cell needs to then be manually changed. We are looking at a way of automating this and thought using conditional formatting might work, so that if the date falls within a certain range it highlights the relevant column (Screenshot attached).

Conditional Formatting.jpg

We need to do this across multiple sheets so are wondering if there is any method where a Template or Master Sheet can be setup and the Conditional Formatting can then be applied to certain sheets without having to update each individually? Would Control Centre allow us to do this?

Tags:
· Share on Facebook Share on Twitter

Answers

Categories

What type of plan are you on? Certain plan types require that a system admin give you permission to access group management.<\/p>


<\/p>

\n
\n \n \"image.png\"<\/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":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":109197,"type":"question","name":"Forms","excerpt":"I created two different forms and the data entered on both of them is saved in the same sheet. How do I get it to save on two separate sheets?","snippet":"I created two different forms and the data entered on both of them is saved in the same sheet. How do I get it to save on two separate sheets?","categoryID":321,"dateInserted":"2023-08-21T14:53:07+00:00","dateUpdated":null,"dateLastComment":"2023-08-21T21:46:32+00:00","insertUserID":165409,"insertUser":{"userID":165409,"name":"mpatton","url":"https:\/\/community.smartsheet.com\/profile\/mpatton","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zDPQszzjyv0!iTHSYpyQ8H0!4mIm1J-gpMN","dateLastActive":"2023-08-21T23:16:32+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-21T22:29:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":25,"score":null,"hot":3385286979,"url":"https:\/\/community.smartsheet.com\/discussion\/109197\/forms","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109197\/forms","format":"Rich","lastPost":{"discussionID":109197,"commentID":391727,"name":"Re: Forms","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391727#Comment_391727","dateInserted":"2023-08-21T21:46:32+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-21T22:29:51+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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-21T20:23:16+00:00","dateAnswered":"2023-08-21T19:38:23+00:00","acceptedAnswers":[{"commentID":391698,"body":"

You would create the sheet first, then you can click on \"Forms\" in the top right corner of the new sheet to create the form.<\/p>


<\/p>

If you need a bunch of the same columns, You can \"Save as new\" the existing sheet and then just delete whatever you don't need from each sheet.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":109205,"type":"question","name":"Auto Populate Column Data","excerpt":"I have a sheet that I am populating row one with data inputted using an intake form (Form1). I then have a Update form (Form2) that is used by end users to input additional data. I need the data inputted from form1 to auto populate the entire column the data resides in when form2 is submitted. Any suggestions on how to…","snippet":"I have a sheet that I am populating row one with data inputted using an intake form (Form1). I then have a Update form (Form2) that is used by end users to input additional data.…","categoryID":321,"dateInserted":"2023-08-21T16:13:10+00:00","dateUpdated":null,"dateLastComment":"2023-08-21T19:37:00+00:00","insertUserID":162922,"insertUser":{"userID":162922,"name":"MSGFreebird","title":"","url":"https:\/\/community.smartsheet.com\/profile\/MSGFreebird","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtfyipG4v_0PcUXERFavMo9q8-BAK6RWzljovF2sarPztFY=s96-c","dateLastActive":"2023-08-21T22:54:52+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-21T22:29:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":39,"score":null,"hot":3385285210,"url":"https:\/\/community.smartsheet.com\/discussion\/109205\/auto-populate-column-data","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109205\/auto-populate-column-data","format":"Rich","lastPost":{"discussionID":109205,"commentID":391697,"name":"Re: Auto Populate Column Data","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391697#Comment_391697","dateInserted":"2023-08-21T19:37:00+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-21T22:29:51+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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/QSNSANMKG9JV\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-21T18:39:09+00:00","dateAnswered":"2023-08-21T18:00:47+00:00","acceptedAnswers":[{"commentID":391672,"body":"

Yes. The \"@row\" piece of the formula is looking for the cell within the row of the formula. So on any row where the meta columns are blank, \"@row\" will also return blank.<\/p>


<\/p>

Try this instead:<\/p>

=INDEX([AT-Meta]:[AT-Meta], 1)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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=321&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":4964,"limit":3},"title":"Trending in Smartsheet Basics","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Smartsheet Basics

  • Creating a contact group in Smartsheet?

    嗨,我想创建一个联络小组(即组织p of collaborator emails). I came across a Help post that instructs me to click on the Account Icon and select "Group Management", however, when I click on the icon, I don't have that option. If I select "My Smartsheet Contacts", there is also no option to create contact…

  • Forms

    I created two different forms and the data entered on both of them is saved in the same sheet. How do I get it to save on two separate sheets?

  • Auto Populate Column Data

    I have a sheet that I am populating row one with data inputted using an intake form (Form1). I then have a Update form (Form2) that is used by end users to input additional data. I need the data inputted from form1 to auto populate the entire column the data resides in when form2 is submitted. Any suggestions on how to…

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video