Losing the will to live with Smartsheet. Please help

Question

This is my second time giving Smartsheet a chance and it's going to take a lot for someone to convince me this platform is any good whatsoever.

The Problem:

I created a Portfolio from their template, and all fine as I was working through etc., except for the project plan template. For some reason, when I get to a certain row using the very in-built template from Smartsheet that they created, the formulas break due to a data-type error, even though it's all the same data-type (a date). See screenshot. What I have tried:

* Reformatting column
* Making sure dates are correct / data type is correct.
* Checked the built-in formula to see if any errors (there isn't, it's identical for the column).
* Deleted my copy/pasted data, adding new rows manually, manually entering dates etc.
* I am the owner and Admin of this Portfolio, however, I do not have the ability to change or update formulas in the sheet(!?)

I've tried it all. It's draining how poor this platform is. I am close to throwing my laptop at a wall and it's only 10am.

It's almost like everything that would appear to be common-sense was forgotten when they built this platform.

Somebody please tell me how to fix this because our Enterprise Acc Manager at SS is also useless and doesn't respond.

Paul Newcome · Answer

Right. I understand it was part of the template. My apologies. When I mentioned "people", I was specifically referring to whoever built the actual template. I see it very frequently and hate that this has gained traction as a "solution".

There should also be an automation in place set to run daily. That will keep that column populated (daily of course) which refreshes the TODAY() function.

The TODAY() function is what should be used in the formulas. I recommend digging into the sheet and making that swap wherever you find it including in the template so that future projects don't have that issue.

Tom Williams · Answer

Hi Paul - appreciate the response, but to clarify, I am using the pre-built PMO template and project plan template within the Portfolio workspaces. So where you say you're not sure why people keep doing this, perhaps Smartsheet need to update the formulas, as this is nothing to do with any formulas I've written. It's also not an issue with updating, but more the fact Smartsheet pre-populate the today's date column, but it's hidden. the Screenshots below probably give a better overview.

This is the template that Smartsheet provide for projects in the Portfolio workspace. Naturally, I deleted the placeholders and started adding my plan detail. These are the only columns on display.

As you can see next, when I start populating the dates beyond the line items that are pre-populated, it automatically starts throwing out the '#INVALID DATA' in the cells.

Given that I had no idea it was already referencing a hidden column, how is a user supposed to know where the cell is being reference? The help section just says "It's referencing an incorrect cell" or something similar. Not helpful at all. Only once I clicked unhide columns (and not sure why they are hidden by default in the first place if they are being referenced) did I see that the 'Today's date' column existed in the plan template and is being referenced, and that it is only populated alongside the pre-populated placeholder plan line items. Screenshot below.

So I've now simply dragged this column to match the number of line items in my plan.

Paul Newcome · Answer

@Tom Williams This exact issue is something I have seen but can't figure out why people keep doing it.

Some background: The TODAY() function does not automatically update on the back-end every day. It will only update when the sheet is activated. There are a number of ways to do this, but one of the more increasingly popular ways (and my preferred method as well) is to insert a date type column and then use a daily Record A Date automation to record the current date in that column every day. This activates the sheet which in turn updates teh TODAY() function.

Here is what I see that I don't understand… People reference the date column in their formulas. This causes the exact problem you saw where formulas will error until the next time the automation runs.

What should be done: Just use the TODAY() function in the formulas.

My suggestion: Explore the formulas in the sheet and replace any reference to that column that is supposed to house today's date with TODAY().