Rich Formatting On "Summary Sheet"

I'm stuck! I am effectively trying to create a Sheet Summary report with conditional formatting, however, I understand that is not an available feature. Instead, I am trying to create a sheet (not a report) with formulas that pull from multiple sheets and can be formatted.

I have 20-30 real estate development/construction projects that I am trying to include in this roll-up. Each project has about 80 individual tasks with start dates, end dates, durations and predecessors. How can I create a line in a roll-up sheet for each project (sheet) and have it pull the same dates for each project.

My goal is to view the same info from each project on one sheet.

Currently, I have a lengthy sheet summary for every project and have those mapped to a sheet summary report (but again no, conditional formatting...which is brutal).

Many thanks!


This is a screenshot of the individual project sheet:


Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @GabeHarris

    I hope you're well and safe!

    Here's a possible workaround or workarounds

    • Link your sheet summary fields to new columns in the sheet (on one row), and then you can use that for the Report.
    • Link your cells in the sheet to the same new columns as the previous option.
    • Create a so-called helper sheet, I'd call it Master Metrics Sheet, and then you'd use cross-sheet formulas to collect the information from each sheet.
    • Same as above, but you'd create the new columns and then use cell-linking from each sheet instead to collect the information.

    Would any of those options work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Thanks for responding so quickly @Andrée Starå! Cell linking is certainly an option but it seems very manual. Is there a faster way to link cells in bulk, or choose to link the same rows/cells from multiple sheets so when I add a new line in the summary I can just change the reference sheet name?

  • @Andrée Starå I'm also very interested in knowing if there's a less manual way. I'm have sheets that are being continuously updated by the team, and I need my reports to continue to take in these new fields. Manually linking would be very difficult.