Date Grouping for Gantt Dashboard

elicos
elicos ✭✭✭
inFormulas and Functions

Hi Everyone,

I'm trying to automate a sheet into a Gantt Dashboard, however I am having issues finding a work around to show the child and parent dates, as all the information is stored in one row, rather than 3 rows for example.

Sample Column Names: Project Name, Task 1 Start Date, Task 1 End Date, Task 2 Start Date, Task 2 End Date, Project Start, Project End

So I need some way to group the dates under 3 rows, for example Project Name, Project Name Task 1, Project Name Task 2.

Is there a work-around this? I have not been able to find anything so far.

Best,

Tags:
· Share on Facebook Share on Twitter

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi@elicos

    Not sure I follow.

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or somescreenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too,[email protected])

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    ✅Did my post(s) help or answer your question or solve your problem? Please help the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter
  • elicos
    elicos ✭✭✭

    Hi Andree,

    Was hoping you would respond to this post as I've noticed you are very knowledgeable in Smartsheet capabilities and work-arounds.


    I have a data table in this format with over 200 row entries that is being updated by different teams, therefore it has to stay in this format.


    image.png


    I am trying to get it into a Gantt dashboard, see below for the required format.


    image.png


    Not sure if this grouping function is possible at all.


    Thanks,

    · Share on Facebook Share on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @elicos

    Thanks for the nice words.

    You'd need to use a so-called helper sheet with either cell-linking or cross-sheet formulas to restructure the Gantt.

    Another option could be to do the same on the same sheet in another section.

    Make sense?

    Would that work/help?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter
  • elicos
    elicos ✭✭✭

    Thanks Andree, yes that would work but it would be manual linking right? Is there a way to do this automated? as new projects get added.

    Maybe there is no solution.


    Thanks,

    · Share on Facebook Share on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @elicos

    Excellent!

    You're more than welcome!

    Yes, cell-linking would be manual, but cross-sheet formulas would need to be set up only once on each sheet.

    Make sense?

    ✅Remember!Did my post(s) help or answer your question or solve your problem? Please help the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
Hi @Sam Swain<\/a>,<\/p>

You should be able to use this formula to accomplish this:<\/p>

=IF(HAS([Type of Project]@row, \"Consolidation\"), \"🝢\", \"//m.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"Reduction\"), \"︾\", \"//m.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"New\"), \"○\", \"//m.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"Termination\"), \"⨷\", \"//m.santa-greenland.com/community/discussion/comment/\")<\/p>

Example output:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope this helps - if there are any issues etc. then just post! ☺️<\/span><\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106930,"type":"question","name":"Help with date formate","excerpt":"I have my months extracted from a date column using =Left(EISD@row,2) I need a formula to evaluate my month column, if it's 01-06 it returns an S, if its 07-12 (or <=7) then it returns an F.","snippet":"I have my months extracted from a date column using =Left(EISD@row,2) I need a formula to evaluate my month column, if it's 01-06 it returns an S, if its 07-12 (or <=7) then it…","categoryID":322,"dateInserted":"2023-06-26T22:42:11+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T12:53:54+00:00","insertUserID":157924,"insertUser":{"userID":157924,"name":"jpaul","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/jpaul","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/34U84XEA80NI\/nJBANQUTUTY9A.png","dateLastActive":"2023-06-27T18:03:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":157924,"lastUser":{"userID":157924,"name":"jpaul","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/jpaul","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/34U84XEA80NI\/nJBANQUTUTY9A.png","dateLastActive":"2023-06-27T18:03:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":25,"score":null,"hot":3375690965,"url":"https:\/\/community.smartsheet.com\/discussion\/106930\/help-with-date-formate","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106930\/help-with-date-formate","format":"Rich","lastPost":{"discussionID":106930,"commentID":382517,"name":"Re: Help with date formate","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382517#Comment_382517","dateInserted":"2023-06-27T12:53:54+00:00","insertUserID":157924,"insertUser":{"userID":157924,"name":"jpaul","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/jpaul","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/34U84XEA80NI\/nJBANQUTUTY9A.png","dateLastActive":"2023-06-27T18:03:26+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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-27T12:53:24+00:00","dateAnswered":"2023-06-26T23:07:25+00:00","acceptedAnswers":[{"commentID":382460,"body":"

Assuming your result will only ever be 01-12, this should do the trick. The VALUE() function may not necessarily be needed, but I'm not sure what type of date you are pulling the month information from and it isn't going to hurt anything.<\/p>

=IF([Month Column]@row <> \"//m.santa-greenland.com/community/discussion/comment/\", IF(VALUE([Month Column]@row) < 7, \"S\", \"F\"))<\/p>

Replace [Month Column] with your column name.<\/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":106913,"type":"question","name":"SUMIF with a checkbox","excerpt":"I'm trying to sum a row of amounts (Parking Revenue Regular:Private boat parking revenue) into the column \"Total Parking Revenue\", but only if the checkbox \"Payment Voucher\" isn't checked. When I add the rows using this formula without the checkbox, it works (formula on first row only): =SUM([Parking Revenue…","snippet":"I'm trying to sum a row of amounts (Parking Revenue Regular:Private boat parking revenue) into the column \"Total Parking Revenue\", but only if the checkbox \"Payment Voucher\" isn't…","categoryID":322,"dateInserted":"2023-06-26T18:20:45+00:00","dateUpdated":null,"dateLastComment":"2023-06-26T19:03:27+00:00","insertUserID":162776,"insertUser":{"userID":162776,"name":"declark","title":"Regional Director","url":"https:\/\/community.smartsheet.com\/profile\/declark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!4cFWuLGjTPw!x-tXMZGznvw!Dlin9vWXHb9","dateLastActive":"2023-06-27T14:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162776,"lastUser":{"userID":162776,"name":"declark","title":"Regional Director","url":"https:\/\/community.smartsheet.com\/profile\/declark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!4cFWuLGjTPw!x-tXMZGznvw!Dlin9vWXHb9","dateLastActive":"2023-06-27T14:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":33,"score":null,"hot":3375611052,"url":"https:\/\/community.smartsheet.com\/discussion\/106913\/sumif-with-a-checkbox","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106913\/sumif-with-a-checkbox","format":"Rich","lastPost":{"discussionID":106913,"commentID":382400,"name":"Re: SUMIF with a checkbox","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382400#Comment_382400","dateInserted":"2023-06-26T19:03:27+00:00","insertUserID":162776,"insertUser":{"userID":162776,"name":"declark","title":"Regional Director","url":"https:\/\/community.smartsheet.com\/profile\/declark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!4cFWuLGjTPw!x-tXMZGznvw!Dlin9vWXHb9","dateLastActive":"2023-06-27T14:32:22+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\/3KE67LQDZX4M\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-26T19:03:34+00:00","dateAnswered":"2023-06-26T18:31:26+00:00","acceptedAnswers":[{"commentID":382384,"body":"

Try IF([payment voucher]@row=0,Sum([Parking Revenue Regular]@row:[Private boat parking revenue]@row),\"//m.santa-greenland.com/community/discussion/comment/\")<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • IF cell CONTAINS one/two/three different values, return corresponding values

    Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria "Consolidation", "Reduction", "Termination", "New" Currently the formula works for single values with the following formula =IF([Type of Project]@row = "Consolidation", "",…

  • Help with date formate

    I have my months extracted from a date column using =Left(EISD@row,2) I need a formula to evaluate my month column, if it's 01-06 it returns an S, if its 07-12 (or <=7) then it returns an F.

  • SUMIF with a checkbox

    I'm trying to sum a row of amounts (Parking Revenue Regular:Private boat parking revenue) into the column "Total Parking Revenue", but only if the checkbox "Payment Voucher" isn't checked. When I add the rows using this formula without the checkbox, it works (formula on first row only): =SUM([Parking Revenue…