Looking for suggestions - master formula errors report

JLC

Hi all, looking for suggestions based on others' experiences. We have a series of dashboards and their respective back end sheets that occasionally throw formula errors for various reasons. I'm hoping I can leach some ideas from the Community on creating a master report/notification that informs us when any error occurs. 

The easiest thing I can think of so far is to add a row to each of the back end sheets, with maybe a checkbox column, that checks if there's anything in the whole sheet that includes "#" (we don't use this character for any of our metrics on dashboards so the chances of it pulling something that's NOT an error is slim). We could then create a report that pulls any sheet name where that box is checked so we can quickly diagnose and correct the issue.

We have formulas on any number of columns across ~50 sheets so far, so creating an actual Smartsheet report isn't an option.

Has anybody implemented an easier solution? This wouldn't really be difficult going forward when making new back end sheets, but wondering if we can find a better solution before I add this row to the ~50 existing sheets.

Thanks in advance!

Paul Newcome

I have been working on a way to imlement somethign similar as well. The problem I have run into is that IFERROR and ISERROR statements cannot compensate for all errors.

 

=pkjjufi

 

will give you #UNPARSEABLE

.

If you try to reference this cell in any way to include 

 

=IF(ISERROR(..................), .............)

=IFERROR(..........., ..........)

=FIND("#", ...............)

=IF(CONTAINS("#", ................), ................)

 

then you will receive a #BLOCKED error in place of this second formula.

 

You could (untested because I just now thought of it while typing this out) us an IFERROR statement to generate "#BLOCKED" if there is an error and then build your workflow to send a notification on rows where Error Trigger Column equals #BLOCKED.

 

=IFERROR(JOIN([First Column]@row:[Last Column]@row), "#BLOCKED")

.

Theoretically this should provide the same error code whether it be because there is some other error that is being corrected by the IFERROR or because there is an error that cannot be corrected and in turn generates the #BLOCKED error.

 

Again... This is completely untested as I just thought of it while typing up this reply. I will revisit this post when I get a chance to test.

Paul Newcome

This worked for me!

 

You can also run a report on whether or not that column contains "#". I modified my setup a bit though.

 

I have my first column [Error] looking across the rows. I then have another column [Error Report] that has essentially the same formula except it looks down my [Error] column. I built this into a handful of sheets (and plan to build it into all sheets for a particular workspace). 

 

I set up a report to look at the entire workspace and pull all rows where [Error Report] contains #.

 

This means that I get a single row for each sheet that has an error. I can then leverage the [Sheet Name] column in the report to jump to each sheet to correct any issues.

 

I set up the report to send as a PDF each morning and included a hyperlink in the message field to where I can access the report through a single click from the email if any rows are populated (meaning a sheet somewhere contains an error). If I check the email and the pdf is blank, then all sheets are good and I can just dismiss the email.

.

I hope this helps!

JLC

Hi Paul, I admit I haven't had a chance to look at this yet as we've moved on to some other things (this is just a pet project for me). I really appreciate your input and can't wait to dig in to this once I have  a chance!

Paul Newcome

No worries. It actually helped me figure out a better way of doing things than I had previously been doing them. I have actually implemented something very similar to this on all sheets across the main workspace that I use and have reports sent on a schedule that tell me which (if any) sheets have errors on them within the workspace. I am actually really liking it because it helps me make sure that I have updated everything tied to a specific metrics sheet or if something has been broken. By using reports, I can also jump straight to the sheet in question by leveraging the sheet name column. It has made life so much easier as far as keeping an eye on things is concerned.

JLC

Hey Paul, had an idea the other day. Haven't been able to really test it out but thought it worth mentioning. Wondering if the new Sheet Summary function can be of use here. I have around 15 dashboards that need to be clear of any #CALCULATING errors at all times. From there there's about ~20 sheets we colloquially call "databases" (back ends of the dashboards that house metrics) as well as ~5 "master sheets" (where the raw data comes in - project lists, resource time lists, tactic lists, etc). I'm thinking I may add a formula to the sheet summaries of each of those to COUNTIF anything in the range of Column A to Column Z shows "#CALCULATING". Then, I'd add those to a master dashboard showing the status of all the major sheets. 

I think in the end your suggestion would be easier and more helpful but I just thought I'd mention it in case it helps get some more ideas going! The best part of your suggestion is using the sheet name column to navigate directly to the sheet in question. Love that - so efficient.

JLC

@Paul Newcome I'm about to finally implement this "pet project" into our master sheets. Just wanted to check in and see if you had any other thoughts or ways or working over the past few months before I go ahead? At this point I'm thinking of foregoing a master report and instead just setting up one row in each of our master sheets that scans that whole sheet for any errors. Having some trouble with ISERROR and IFERROR for the reasons you already listed above. Find any other alternatives or still loving the reports way?

Paul Newcome

I am still using my previously mentioned solution. I definitely like the reports because of the sheet name column that is built in.

I do suggest being wary of one row that scans an entire sheet as functions can only evaluate 4,000 characters at a time. I had initially done it that way, but then found that when looking across entire sheets I exceeded that and things were missed. I also include a column that I put on every sheet that replicates the row number so that my report can show me not only which sheet has the error but which row.