Linking External Sheets for Conditional Formatting
I am currently working on conditional formatting for a spreadsheet that connects to several spreadsheets. We have a Master Log (spreadsheet) that pulls project dates from multiple Project Schedules (Gantts) so we can keep track of project timelines of milestones.
We have roughly 45 fields. 17 fields are linked to Project Schedule dates: a 'Go-Live' date, multiple milestones, and the 'Post Go-Live' date. The goal is to use conditional formatting for each milestone relative to initial Go-Live (Red=past due date and <100% complete, Green=still have time to complete and <100%, and Blue=100% completed). We want the cell to display the date from the Gantt, but be highlighted using the % completion in the same row as the date.
Initially I created a bunch of conditional formatting using an internal formula saying if project is going over the anticipated milestone time frame, make this red until complete. In a discussion with my co-worker, we realized this is not the best method.
What I am trying to figure out is if there is a way to base a cell's conditional formatting on a cell in a separate sheet. As an example, since the Master Log has several projects, I want to link the cells of individual Project Plan into the Master log and have it reflect "For Project A, Milestone 1 is 100%, therefore the cell is BLUE, Milestone 2 is 100%, BLUE, Milestone 3 is 80% and behind schedule, RED, Milestone 4 is 20%, GREEN." However, I am unsure how I can create conditional formatting that uses a formula in Smartsheet.
If it helps, we do have a column that links the Project Schedule where the Milestone data comes from that way if a PM needs to quick reference, it's readily available. I'm not sure if there is a VLOOKUP method that can be used or if there needs to be a lot of manual referencing done.
Any guidance is appreciated. Happy to explain more.
Help Article Resources
Check out the Formula Handbook template!