HELP with a large sheet and table layout/conditional formatting

Melisa Dannhauser

I spent 15 hours trying to organize my data in an efficient way and just can't come up with a good solution. Smartsheet is great but SO limiting if you are used to Excel.

I have 200+ sites that each has 31 activities. Each activity has 3 dates - planned date, reforecasted date and actual end date. I need a report that shows just one date per activity and color codes it based on which colum it is pulling from. I created a column named "Reported Date" that pulls the appropriate date from the 3 date columns based on a formula. Then I have 3 conditional formatting rules for that column that color the dates gray if they are completed, red if they are over due and yellow if they are upcoming.

Problem I have is that I need 2 versions of the table. One that has the addresses and activities side by side (first table in my screen shot) and one that shows the addresses in rows but hte activities in columns (second table in my screen shot). The team needs to update the dates in the first table but the client needs to see the table in a format like I have in the second table.

I can get the data to transfer into the new format (second table) just fine with formulas. The problem I have is the conditional formatting. HOW can I get the dates to have the same conditional formatting without having to create 93 conditional formatting rules AND several helper columns?? When I do that, my sheet is MASSIVE and slow and just in general this is NOT best practice when you have so much data, it's a recipe for disaster.

I was hoping I can pull a report from the first table once created but reports don't let you transpose the data in a way that I need. And conditional formatting in Smartsheet is based on columns so if I have 30 columns and 3 formatting rules per column (gray, red and yellow) then I need 90 rules. Plus in order for the sheet to recognize which date is gray/complete, I need a helper column for EACH cell. Like I said, MASSIVE sheet for no reason except that Smartsheet isn't SMART.

Any other ideas how I can organize my data to achieve this? So I have data in my first table that has thousands of rows. I need it to transpose to only 200 rows with 31 columns. The only issue I'm encountering is the color coding! I'm thinking this is impossible in Smartsheet but wanted to check before I completely give up.

Thank you!!

DJB

I'm not feeling very smart either. I'm struggling to understand. How would you have done this in excel?

If i do understand correctly, you're saying your options / limitations are:

1. if you use a Report it copies over the formatting but you can't rearrange (or aggregate?) the data.
2. If you transpose the data in another sheet, you can transpose it but you have to set up the three formatting rules for each of your 30 date columns (i only see two in the second example, but there are 30? IE Activities A through AD?)

If that's the case, i don't see how you would avoid the 90 conditional formatting rules. However, transposing data doesn't seem like a great strategy. 'Reports' in Smartsheets aren't technically reports (in that they aggregate data), they are just reorganized summaries of the main sheet. If you were doing this in excel, would you be using a pivot table report and aggregating all the data horizontally for each address? I'm guessing excel allows you to apply your three rules to multiple columns instead of just one at a time. In situations where i needed to pivot data i would export to excel, but i see then you wouldn't be able to update information.

Melisa Dannhauser

If I was using Excel, I would be doing the same thing as here except I would set up ONE conditional formatting rule that applies to the whole sheet and be done.

Yes, there are 31 activities (each activity has 3 dates), and 210 sites, the data is confidential so I made up a little mock up and shortened it.

I guess I'm trying to understand if there are other options for me that I might not be aware of in Smartsheet. Like is there any way I can create a report that is transposed? As in, I would only have the FIRST table from the example above and use that to create a transposed report, as opposed to having to create all the formulas to get the data to appear in the form like the second table.

And if that's not possible, I'm OK with having to transpose the data with formulas to create the second table. But what can I do to avoid having to create 90 conditional formatting rules and helper columns? I'm guessing nothing but just wanted to double check.