#INVALID VALUE Error on Report but Grid shows correct result

method90

Hi Everyone,

Having an issue with my report showing #INVALID VALUE when a new submission is made on a form. I have my formulas set up in the same form where I track each row item being submitted, so unsure if this is the issue.

See below:

What it should display:

The issue I find is when a new submission is made via the form, I need to open the grid view and click save for the report/dashboard to update. Why is that?

Thanks,

Fellow Smartsheet-er

Mike TV

@method90

I've seen this problem before on some sheets but haven't quite located the reason just opening a sheet makes it think I did something that needs to save. One problem I've seen on our sheets is a Batch Name that's a JOIN formula with a date at the front (such as 051322ABC-RES) would end up with the date at the front reversed like European formatted dates and when you load up the sheet it immediately thinks you changed something but when you hit save it would fix the Batch Name date format to how it should be. Not sure what causes that.

Anyways, it looks like the column with your error code has cell formulas instead of a whole column formula, is that correct? If so, have you tried making that formula a column formula and does it act properly like that instead?

method90

Hi @Matt C.,

I tried the column formula approach but I have cell and absolute references in my formulas so it doesn't allow me to switch over.

Is there a way I could transfer my formulas to a new sheet and reference my formulas to my submission sheet then create a report from that?

Otherwise, I'll try a summary fields approach and see how that goes.

method90

More detail:

When a new submission is made via the form; the grid view updates as so:

Because the CONCAT columns turn to #INVALID VALUE and other turns to #BLOCKED then the report values turn to INVALID VALUES as well. With that said, the minute I go into the grid view and simply click "save" the formulas update and the INVALID/BLOCKED ERRORS disappear and show as so:

method90

Additional troubleshooting details:

When a new submission is made, as mentioned, then majority of rows flip from working to #INVALID VALUE/OPERATIONS:

I scrolled to my formula(s)/helper columns and noticed the following:

Even though I'm using the same formula in these columns I'm getting a different result. The result that should come up is the third row (latest submission) and the two rows above are previous submissions that have been glitched. The formula as you can see has a comma in the time pull formula for whatever reason.

Any idea?

I'll continue troubleshooting and wait for any comments from the community.

Mike TV

@method90

https://community.smartsheet.com/discussion/66008/why-sheet-requires-saving-when-nothing-changed

Check out this community post from a couple years ago. It sounds like the wanting to Save when viewing the sheet is possibly caused by a Created Date and/or Modified Date column with formulas written to interact with them.

Maybe you can set up an automation for when a new row is added to the sheet, copy it to another sheet, do your calculations on the date column(s), and then use a cell reference in the master sheet to get the data you needed from the date column formula(s). Possibly if you do that, maybe this other column formula won't be having errors until the sheet it loaded/saved?

It's a long shot but you never know.

method90

Ok, so issue was in the comma being added in the Created At column so I used a LEN helper column to count if its 16 or 17 length (Created At) then used an IF formula to pull the time from the date/time field. i.e. if its 16 then pulls 7 characters from the right otherwise pulls 8.

After this, I don't get any errors in my formulas therefore dashboard updates properly without and #errors.

Hope this helps!

Mike TV

@method90

So that shows it was a formula designed around the Created Date (or Modified Date) column which is what that other thread indicated. Good to know. Sounds like it is ultimately a bug in SmartSheet that needs fixed though.

@Genevieve P.