Linking External Sheets for Conditional Formatting

Terry H

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.

Paul Newcome

Are you able to provide some screenshots for reference/context?

Terry H

Hi Paul!

Of course- I can definitely provide screenshots and give more context.

The first image is of the Master Log with some sample milestones we track. The milestones are linked to a separate spreadsheet (Individual Project) that you will see in the second image.

What I am looking to do is that if the "Email Support" Column in the Individual Project is marked as 100% complete, then the linked cell in the Master Log (above) will be blue. If the project is not 100% complete, and is before the completion date, such as the second two columns, then they will be marked Green.

The second part is that the Master Log connections with multiple other sheets (1 to Many) so I am not sure if there is a function that will allow us to do this automatically. I am happy to talk this through if it is easier.

EDIT: If it helps, another way to think of it is like a formula. Each cell should needs a formula along the lines of "Look at the corresponding 'Opportunity' spreadsheet within the folder, look at Row ##, and If 'Completion'=100% AND is <='Date", Blue, etc etc..".

Thank you!!

Paul Newcome

You would need to include additional columns to pull in the due date and % Complete to the Master Sheet.

Terry H

Hi Paul,

Thank you for the heads up! Is there a way to automate the linking in that case? I can do the Conditional Formatting easily, it's the manual connecting we are looking to simplify.

Paul Newcome

My suggestion would be to insert a series of hidden columns on each individual sheet. Have them named the same and in the same order as the master sheet. In the top row, use cell references to pull the data points from the main body of the sheet to these helper columns. Once set up, you can hide them.

On the Master Sheet you would select all of the linking cells in the row, right click and select the option for cell linking, select the appropriate sheet, select the cells there in the top row of the helper columns, and you can create all of the cell links for that one sheet in a single go instead of having to link every cell individually.