Moving specific column info while retaining formulas in other columns
Hello Everyone,
I'm relatively new to Smartsheet. I've created a sheet to track items as they go through a review process. Items must go through the complete review process before going to a Board for approval. The Board meets every two weeks. I've arranged the sheet rows by Presentation Date. The first six or so columns have info specific to the Item - , Targeted Presentation Date, Dept, Item title, Contacts, Current Status (Review stage #1, Review Stage #2, etc.). I've programmed the columns beyond that (starting with Review #1 (Date)) to change color based on the if the item is on time (green), approaching the review deadline (yellow), or has passed the deadline. When the review is completed, the review date is entered in the column, which turns it blue. There are a LOT of hidden helper columns and conditional formatting
Here's the problem: When my team meets to review these, we frequently need to revise the targeted Presentation Dates. For example, an Item scheduled for 2/11/25 may need to be pushed to 2/25/2025. I can't copy and drag the row under the revised Presentation Date because it will take all of the formulas in the row with it and will still show colors based on the original row. For example, I moved Item #9 from 2/25/2025 to 3/11/2025. You can see the red cell indicators still match with the 2/25/2025, not the 3/11/2025:
I'm looking for a way to easily move only the first six columns under the corresponding Presentation Date while not affecting the conditional formatting. Any advice or suggestions would be greatly appreciated.
Thank You
Answers
-
How are you creating the formulas in your helper columns and the rules for your conditional formatting?
If you use [Present Date]@row then the cells should update as soon as you change the date in the Present Date cell. You can then move the rows around as much as you want as the position has no impact on your formula.
Does that make sense? If not, please share the formula in your hidden columns.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!