Unexpected behavior. (Found the Issue)

[Update: I've found the solution and posted it in a comment]

I am having some issues with a very complicated set of sheets I have created. Please be warned, I am stressing the limits of smartsheets, and it is a rather complicated group of formulas. That said, information is below.

Background:

I am trying to optimize a set of reports that are generated 3 times a day summarizing our progress. In this particular instance, I am looking at 12 different stations that are reporting another 12 sets of information 3 times a day. In order to get this information I created a sheet for the 12 sets of information, and used webforms to collect the information. Next I wanted to analyze and conglomerate this information.

I needed to analyze by each of the 12 categories of data, as well as 3 higher level sorting criteria.

Basically I need to analyze production, Defects, and downtime, and all of their subcategories

In order to do this, I used 4 sheets to sum up the information from each of the 12 input sheets. I had to use 4 separate sheets due to the amount of cross sheet level references I had to build.

Once I had those four sheets built, I made a reference page that all 4 sheets reference to sort their criteria on.

Outline of what I had at this point

1 Analysis Sheet
4 Conglomeration Sheets
12 Input Sheets

Now I can have each station use a webform to update the input sheets. I can track the input sheets, and send alerts when they haven't been updated in a timely manner. I have an analysis sheet that changes the criteria for sorting of 4 subsheets. I've calculated it out to adjust over 40,000 cells. Then I use reports and dashboards to sort and pull the information from the conglomeration sheets

Problem

I am having some unexpected behavior in my formulas, and I can't figure out if it is an issue with my formula or an issue with me using to much processing power. The main problem is I seem to be pulling incorrect dates from the 12 submission sheets.

Formulas

1. The first thing I do is reference the main analysis sheet, and put the inputs to each of the 4 conglomeration sheets via cell links.

2. Then I use that information to return the applicable rows from each of the input sheets using a collect formula, and joining them together. There are 4 of these on each sheet. The below formula is the one for Gear/Pack

=IF(OR([By Operation]1 = "Gear/Pack", [By Operation]1 = "All"), JOIN(COLLECT({15. Gear/Pack Counter}, {15. Gear/Pack Shift}, IF($[By Shift]$1 = "All", OR(@cell = "1st", @cell = "2nd", @cell = "3rd"), @cell = $[By Shift]$1), {15. Gear/Pack Created}, IF(ISNUMBER($[Within Last (Days)]$1), NETDAYS(@cell, TODAY()) <= $[Within Last (Days)]$1, IF(ISBLANK($[Date Range]$2), @cell = $[Date Range]$1, AND(@cell >= $[Date Range]$1, @cell <= $[Date Range]$2)))), ",") + ",", "")

3. I then parse the joined cell out into as many rows as it needs, up to 500.

=IFERROR(VALUE(MID(JOIN([Index Ref]$2:[Index Ref]$5, ","), LEN(JOIN([Index Ref]$7:[Index Ref]10, ",")), FIND(",", JOIN([Index Ref]$2:[Index Ref]$5, ","), LEN(JOIN([Index Ref]$7:[Index Ref]10, ","))) - (LEN(JOIN([Index Ref]$7:[Index Ref]10, ","))))), "")

4. Once that is done, I use these row references in an index match formula with row references to the submission sheets (This one is for the submission date)

=IF(OR(ISBLANK([Index Counter]11), ISBLANK([Index Ref]11)), "", IF([Index Counter]11 = "Final Inspection", INDEX({13. Final Inspection Created}, [Index Ref]11), IF([Index Counter]11 = "Lap/Polish", INDEX({14. Lap/Polish Created}, [Index Ref]11), IF([Index Counter]11 = "Gear/Pack", INDEX({15. Gear/Pack Created}, [Index Ref]11), IF([Index Counter]11 = "X", "")))))

That same formula is used across all of the parameters.

In the end the collect formula in step 2 is doing almost all of my filtering by only posting the applicable rows, and the rest of it is index match grabbing.

I'll show a specific example of what is going wrong in the pictures. You will notice there are no submissions on 7/20 for my test data on the Gear/Pack, but my formulas return 3. the formula shouldn't have access to get any dates outside of the sheet, so the only thing I could think of is that the sheet is for whatever reason rounding the date up.

I know this is a very complicated issue, especially compared to what is normally posted here, but I appreciate any help.

I'm going to have intermittent internet access through next week, but I will do my best to get on and try to respond if anyone has anything for me.

Even if this doesn't get solved, hopefully it helps the community find some alternative uses for smartsheet.

Find more posts tagged with

Formulas

Comments

L_123

I feel like a dunce for this. Took me like 3 days of looking into it, and then as soon as I post this question I think of another test I can do and find out the issue.

There is nothing wrong with the formulas, there is a smartsheet side glitch(?) that rounds the date created cells when cell linked to cells in other sheets. My 7/19 dates were getting rounded up to 7/20.

J. Craig Williams

UTC known bug.

Craig

L_123

Is there a list of the known bugs?

J. Craig Williams

Luke,

One must hope.

Several problems with such a vague question:

Clearly, what I determine is a bug is not what Smartsheet support may determine is a bug. Same holds for any user.This has been proven by (repeated) experience. If the person taking my symptoms and explanations does not believe that the problem is a bug, they are unlikely to classify it is as such.

I define a bug as anything that either produces the incorrect result as defined by the requirement or produces an negative result but follows the spec -- 'unintended consequences'. A bug does not cover items that work as designed but don't do what a particular user may wish it did - those are change requests, enhancement requests, or feature requests. There are gray areas -- a feature that requires a work-around to function well may be a bug, for example. (I'm writing this off the cuff, so apologies for incompleteness)

Assuming the Smartsheet employee(s) do classify it as such, there is likely a process to classify any bug as "won't fix", "can't fix", "might fix", "will fix'. A 'known bug' could be any one of those. And it can often change from one to the other as the situation changes.

Often, a "known bug list" is limited to only the "won't fix" and "can't fix" types.

As such, "a list" is likely, but it is unlikely to be definitive and the whole truth for each user.

Further, it is unlikely that such as list has an easy search mechanism built in. "The user says they did this and that and then this happened" -- see Known Issue 23412. It is probably a Catch-22, if you know about the bug, it is a known issue, if you don't it isn't.

I have my own list, but it started out the digital equivalent of sticky notes tossed into a shoe box and I'm slowly trying to determine if it makes sense to organize them in some fashion, they aren't just Smartsheet related, and they range from "I won't use this product because of X" to something extremely minor.

All that said, it would be nice if there was one.

Anyone want to pay me to develop such a list from the non-employee perspective? It could be combined with the "limitations" and "enhancement requests" lists I keep.

No? Didn't think so.

Cheers,

Craig

L_123

I would definitely like to have something to reference if I had another issue like this so I'm not pulling my hair out thinking it is my fault. I understand why they might think it would look bad to have a publicly posted list of bugs/issues however.

I feel like at any given time there is a number of bugs they are aware of but haven't gotten around to fixing yet, for many different reasons.

Shoot even language parsing wouldn't be that much of an issue if they had the list of bugs, users could simply say they had X issue from the list, and even vote to say they had the issue, much like this forum.

J. Craig Williams

WARNING: Digressions ahead. You've touched a nerve and I'm taking my learning hour to put my thoughts into pixels.

I would like to believe I'm an advanced user type, if not an advanced user for each of the programs I use.

Based on my experience here and in other product forums, I often don't know the right question to ask to find out if it is a known issue or not. The data set I have is skewed, since I only see the questions that I and others post, but of the ones I do see, there is a large percentage of questions that are resolved or aided by RTFM. I read StackOverflow a lot when I run into a concept that I haven't groked from the manual and there's many "this is a duplicate of that question" threads. It could be that most questions are resolved before needing to post them. I doubt it, but have no evidence to prove it.

As regards specifically to Smartsheet, there was a company decision to remove user access to the product roadmap. I understand this from a business perspective, but do not agree with it wholeheartedly. Given the number of different collaboration toolsets and PM products, it make sense to hide your plans from your competitors. Opening up the issue tracking system for items one ISN'T going to work on is along the same lines.

That's why I mentioned a user-perspective list. It is in the interest of all of us to have some means to provide ourselves the tools we need to make our Smartsheet experience better, but the job is too big (costly) for one person to do alone.

I'm also sure that my own list of 'known bugs' is smaller (much smaller) than the complete list, as I don't use every feature (sometimes because they have unresolved bugs and sometimes because they are missing key features to make them viable to me).

Here's the sales pitch. You can stop reading now.

That said, we (my colleagues at Smarter Business Processes and I) do have customers that sometimes bypass the learning process when they start to spin their wheels. If you (specifically) had spent 4 hours trying to fix the problem and saw no end in sight, had contacted us, you likely would have been moving again in half an hour or less. I have many examples of this. A customer contacted Richard and needed a formula fix before her meeting with her boss in two hours. He contacted me, we fixed it, and she went into the meeting prepared. This is not limited to Smartsheet.

I miss the days when I could walk down the hall and barge into Peter's office when my own wheels started to spin. When I had a team of engineers working for me, we had a frequent discussions on how to recognize when you were working on a problem and it was going to take a while and you were working on a problem but had no idea how long it was going to take. It was team policy to avoid the second as much as possible.

In the olden times, this forum used to be monitored full time by one or more Smartsheet employees. It isn't any longer. It is still monitored, but not as the employee's primary purpose. And even if it was, I would not ask that employee to answer the question quickly, but rather watch for the 'urgent' ones and cleanup the unanswered ones. But, given that I'm still getting responses from support saying it might take 96 hours to get back to me, there is currently no way to get urgent support for Smartsheet problems without paying for it. I have access to accounts in all three tiers (Team - going away, Business, and Enterprise) and the support, while more responsive as I go up the tiers, is not noticeably better.

But ... I've tried to pay for answers to other tools and found that I'm usually talking to someone that knows no more than I do and have come away dissatisfied with the pay-for-answers business model (I am not alone, several have gone bust, I believe)

Back, briefly, to the original topic - if such a list existed, I believe (but have no proof) that it would only be used by a limited number of users. This may not directly correlate to value, but it probably does. But what do I know? There sure are a lot of active users on StackOverflow and the guy that co-founded it is retired.

My stomach is rumbling. That's enough.

Craig

L_123

I definitely agree about the RTFM questions, I've taken to simply posting links to some of the answers instead of answering them when I get particularly annoyed by it.

Honestly it feels like smartsheet has something of an identity crisis, it can't decide to be more powerful for advanced and dedicated users, or be easy to use for the basic/new users to bring in more people. Doing both is the obvious answer but that's easier said than done.

All of the companies building these add-ons and solutions for smartsheet to get around the problems is a pretty good example of this. Azuqua, zapier, your company Smarter Business Processes, and others. They make a lot of money reliably manipulating information in smartsheet in ways that would be difficult/impossible to do without help for most users. I know they work with other software as well, but the point stands.

I like the idea of the user-perspective sheet, but it would probably be hard to sell that as something that would increase the profit margin of SmartSheet. I wonder if a summary of the posts here on the forum that depreciates posts with little/no attention, while increasing the visibility of the posts getting more attention could have a similar effect. It might inspire a different type of posting as well. The tags would have to be somewhat more descriptive I think though. That would probably be easier to do than building something from scratch.

J. Craig Williams

re: ....when I get particularly annoyed by it.

A chief difference between a moderator and drive-by-helper like you and me is that we have the luxury of just walking away for a while. If I got paid to answer the posts on the forums, I would devote more time (I have devoted more than I care to admit) to figuring how to provide a better way to get the information into the hands of the users WHEN THE NEED THEM. I am very interested in knowledge bases and other means of content delivery at the point of pain. Obviously, I haven't solved that.

re: ...an identity crisis,

Definitely and definitely a difficult problem to solve and most users won't acknowledge that many of the problems are their fault. We used to describe the simulations that we built as a car, built for the road, built for a certain top speed, built for a certain towing capacity, but the customers thought they had bought a car that could shift easily between on-road and off-road use, go 120 mph or haul 2 tons of wood. Some even thought they'd bought a flying car. But without the expense or effort to bring that to life. I don't expect Smartsheet to do everything I need a PM tool to do. But I do expect it to do what it claims to do well enough to get me a decent ROI from my usage.

re: building these add-ons and solutions,

That's pretty much where I am focussed right now, not only with Smartsheet.

re: profit margin of SmartSheet

Well, they aren't (profitable) yet. That is not intended as anything other than a statement of fact.

I can not believe that a well run reference source companion to Smartsheet would not provide value. The issue is always "but ... enough to warrant financial support?"

re: user-perspective sheet

The inherent problem with any internal tool like this is that managers may not be able to point to "well, visits to this portion of our website (the Community) is up and we also see a corresponding decrease in support requests coming in, but that does not correspond to decreased usage of the platform overall". Everything is connected but tools like this are usually underfunded. Trying to do such a thing outside even the possibility to make those connections is doomed to failure, I think -- if the goal is to make Smartsheet's bottom line brighter. I think the goal has to be user adoption, increased sophistication of the self-grown solutions, or something along those lines.

re: increasing the visibility of the posts

There are problems with the Community, but I don't think this is one of them. At least, not in the way I interpret your statement.

read this: https://www.joelonsoftware.com/2003/03/03/building-communities-with-software/

I don't agree with everything there, but some parts changed my mind.

One thing I would like to see on this Community is better access to the Functionality tags. At this point, they continue to be useless for OUR purposes. I assume they are used behind the scenes, but if I don't gain anything from clicking them, why should I bother?

(There's a rant about surveys there -- I might have posted it to my blog, but it probably is one of those unfinished ones, since I don't get paid to write them)

Apparently, I could keep going, but I won't.

Craig

L_123

Smartsheet isn't really much of an analytical tool, at least for the way that I am using it in the original problem in this thread. So I suppose i'm an end user trying to use a car as a calculator, it works, but not really what it was designed for haha. My sheets wouldn't be nearly as complex if I could reference reports in formulas, but that's another issue.

That article was an interesting read. I disagree with several of his assertations as well, but he was very forward thinking given that it was written over a decade ago.

The visibility of issues in the community would only be an issue if the purpose of the forum were to change to what I was proposing, a source to filter out and find problems and solutions. Right now the community isn't particularly easy to navigate, in fact I use google to find submissions rather than the site itself. It isn't really an issue right now, but if the purpose of the community changed, it would be something that would need to change with it. (Ex. A historical post getting a bunch of posts today. If you aren't already a part of the conversation you'll never know about it)

J. Craig Williams

Another thought came up today on Known Issues.

There is a hidden cost to having to explain, over and over again, that an issue has been identified, what the work-arounds are, if any, and what the state of a fix ("won't", "can't", etc...)

This cost is borne by both the user and the developers/support. If developers/support can at least tag their hours to a bug that won't get fixed, it might raise the priority of fixing it.

(A real life example of this came up today)

Craig

Sign in to join the conversation!

Quick Links

Trending discussions

Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…
Dashboard not updating
Hello Smartsheet Community! I created a formula that tracks the number of days away from the deadline that I posted on my dashboard as a visual for my students. =IF([Deadline Date]@row < TODAY(), "🔴 Past Due (" + ABS([Deadline Date]@row - TODAY()) + " days ago)", IF([Deadline Date]@row = TODAY(), "🟠 Due Today",…
How to use previous cell value (from cell history) in a formula?
I have seen this question asked multiple times in the forum, but I couldn't find any clear answer provided in the answers. Please excuse me if a solution already exists, but if not, will someone please let me know if it is possible to use a previous cell value (from cell history) in a formula? I am specifically trying to…