Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, pleaseVisit the Current Forums.
Past Due Reports
GOAL - Tasks Past Due Report for Multi Sheets
I have several sheets with the columns set up as seen below:
(Type 1) Rows have Tasks with a Contact under Assigned to, "Required" column has a symbol and % Complete is not required so it's left Blank.
(Type 2) Rows have Tasks with a Contact Under Assigned to, "Required" column is left blank because its not required, and the % Complete is required.
I need a report that shows when the End Date on the (Type 2) Rows is past due. I have the Report Builder Set up as:
Who - Assigned To is not Blank
What - % Complete is not blank and not equal to 1
When - End Date - is in the past
My Problem - If the % Complete is blank it gets filtered out.... this is needed because the (Type 1 ) Rows will all be left blank, so filtering them out is correct. I need (Type 2) Rows only to show in the report if % Complete is blank or not 100%.
Example: If someone has a task they havent started but is now past due, it will not show up on my report because the % complete is blank. I could ask everyone to enter 5% to acknowledge their task if i HAVE to but I'm trying to find a work around before I go that route.
Has anything had this sort of situation? Advice?
Thanks!
Comments
-
J. Craig Williams ✭✭✭✭✭✭Laura,
Since [Required] is blank when [% Complete] should not be, you will need a value in the cell.
If adding new rows via a WebForm, set the default value = 0.
If not using Dependencies, you can set the value with a formula.
If you are using Dependencies, you will need to set them manually when setting up the sheet or adding the row.
It seems that either the symbol is there (show) or it isn't.
In that case, if the % complete is blank it is an mistake.
I think I am missing something.
Craig
-
Laura ✭✭✭✭✭✭
Craig - I edited the above, maybe that clarified it a little more if you feel like youre missing something.
Or maybe I'm just brain dead this week and everything will be clear to me on Monday? haha
I'm Not using a webform.
and yes, I am using Dependencies.
-
J. Craig Williams ✭✭✭✭✭✭
Laura,
Are you looking for a Report that shows BOTH Type 1 and 2 or only Type 2?
Craig
-
Laura ✭✭✭✭✭✭
Only Type 2. Sorry!
-
J. Craig Williams ✭✭✭✭✭✭
Laura,
I think this can't be done in the Report Builder.
The one that that is a missing, I believe, is "not blank" in the What? criterion.
The only way to get a "not blank" is to select "blank" and then exclude it.
But that excludes all items, not only one of them.
And we can't have the same % Complete column used in two different criteria, one using exclude and the other not using it.
Looks like you'll need to build your criteria in a formula on your sheet and the Report off that.
Craig
Categories
Try this:<\/p>
=IF([Date of Site Survey (Product)]@row <> \"//www.santa-greenland.com/community/discussion/7744/\", IF(TODAY() > [Date of Site Survey (Product)]@row, \"yes\", \"no\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106277,"type":"question","name":"Help creating formula to update inventory automatically","excerpt":"The following is our brochure requests sheet. Right now we manually update our inventory each time we receive a request. Here is a snapshot of the new inventory sheet. I'd like to create a formula that automatically subtracts 100 from Spanish Qty each time the Request Completed is checked, the Request Denied column is…","categoryID":322,"dateInserted":"2023-06-09T16:17:37+00:00","dateUpdated":null,"dateLastComment":"2023-06-09T18:12:07+00:00","insertUserID":161705,"insertUser":{"userID":161705,"name":"Christina S.","url":"https:\/\/community.smartsheet.com\/profile\/Christina%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T18:07:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161705,"lastUser":{"userID":161705,"name":"Christina S.","url":"https:\/\/community.smartsheet.com\/profile\/Christina%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T18:07:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":37,"score":null,"hot":3372664184,"url":"https:\/\/community.smartsheet.com\/discussion\/106277\/help-creating-formula-to-update-inventory-automatically","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106277\/help-creating-formula-to-update-inventory-automatically","format":"Rich","tagIDs":[204,219,254],"lastPost":{"discussionID":106277,"commentID":379911,"name":"Re: Help creating formula to update inventory automatically","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379911#Comment_379911","dateInserted":"2023-06-09T18:12:07+00:00","insertUserID":161705,"insertUser":{"userID":161705,"name":"Christina S.","url":"https:\/\/community.smartsheet.com\/profile\/Christina%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T18:07:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/FMANSEYA9OS3\/mm-brochures-request-sheet-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"MM-Brochures-Request-Sheet.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-09T18:11:32+00:00","dateAnswered":"2023-06-09T17:51:51+00:00","acceptedAnswers":[{"commentID":379899,"body":"
