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.

Hierarchical List - display and behaviour query

lizat
lizat
edited 12/09/19 inArchived 2016 Posts

I want to put this in Smartsheet and use the expand and collapse capability. However to get the indented list I need the name and number in the primary data column in SS. That is fine but when I export back to excel I will then not be able to know the 'parent' of an item or its level. The simplest thing would be to do as I have in excel and use separate columns for the names and use the primary data column to hold the numeric Id; however SS, unlike Excel, won't (I think) allow the contents of a cell to spill across an adjacent cell even if it is empty .

Can anyone think of a way I can acheive what I want, these are the important things:

  1. a hierarchy list
  2. parent is identifiable
  3. indented
  4. names entirely visible
  5. expand & collaspe
  6. exportable to excel - with parent & level characteristics that can be interrogated and manipulated

If it were only me I would be able to use just SS but I have to supply to others in the business in a form where they can extract the information.

Thank you................................ liz

PI_ForDiscussion.jpg

Comments

  • JohnHinkle
    JohnHinkle ✭✭✭✭✭✭
    edited 05/25/16

    Why dont you use the built in grouping feature in Excel, rather than creating your own system using columns?

    When you export a sheet from Smartsheet that has hierarchies, Excel will automatically use the built in grouping tool. Like this:

    Screen Shot 2016-05-25 at 9.23.13 AM.png

This discussion has been closed.
[Date of Site Survey (Product)]@row, \"yes\", \" \")) Intended logic\/result - If date cell is blank, then \"blank\" (this worked when I tried without the 2nd argument added) If date is in…","categoryID":322,"dateInserted":"2023-06-09T14:00:25+00:00","dateUpdated":null,"dateLastComment":"2023-06-09T23:14:36+00:00","insertUserID":162220,"insertUser":{"userID":162220,"name":"dhawkins","title":"Product Manager","url":"https:\/\/community.smartsheet.com\/profile\/dhawkins","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T20:05:00+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-09T23:38:00+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":29,"score":null,"hot":3372673501,"url":"https:\/\/community.smartsheet.com\/discussion\/106265\/if-date-is-blank-past-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106265\/if-date-is-blank-past-formula","format":"Rich","lastPost":{"discussionID":106265,"commentID":379953,"name":"Re: If Date is blank + past formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379953#Comment_379953","dateInserted":"2023-06-09T23:14:36+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-09T23:38:00+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-09T14:28:35+00:00","dateAnswered":"2023-06-09T14:12:11+00:00","acceptedAnswers":[{"commentID":379830,"body":"

Try this:<\/p>

=IF([Date of Site Survey (Product)]@row <> \"//www.santa-greenland.com/community/discussion/5075/\", 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":35,"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":"

@Christina S.<\/a> <\/p>

Switch your formula to:<\/p>

=[Spanish Qty Baseline]# - (COUNTIFS({Brochure Requests Sheet Request Completed}, 1, {Brochure Requests Sheet Request Denied}, ISBLANK(@cell), {Brochure Requests Sheet Brochures Confirmation}, CONTAINS(\"Spanish Brochures\", @cell), {Brochure Requests Sheet Submission Date}, >DATE(2023, 6, 8)) * 100)<\/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":204,"urlcode":"Forms","name":"Forms"},{"tagID":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106270,"type":"question","name":"Completely stumped on this formula","excerpt":"Hello, I'm trying to be able to identify when an upsell comes through for a specific function. But so far everything I've tried hasn't worked. The sheet I want the information to show up on isn't the sheet that has the data so I have to reference a separate sheet. But basically what I'm trying to do is: =IF(AND({Customer…","categoryID":322,"dateInserted":"2023-06-09T14:28:33+00:00","dateUpdated":null,"dateLastComment":"2023-06-09T19:58:02+00:00","insertUserID":151459,"insertUser":{"userID":151459,"name":"Paige_Hamby","url":"https:\/\/community.smartsheet.com\/profile\/Paige_Hamby","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/n7ZG0TBD44T33.png","dateLastActive":"2023-06-09T19:56:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":151459,"lastUser":{"userID":151459,"name":"Paige_Hamby","url":"https:\/\/community.smartsheet.com\/profile\/Paige_Hamby","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/n7ZG0TBD44T33.png","dateLastActive":"2023-06-09T19:56:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":32,"score":null,"hot":3372663995,"url":"https:\/\/community.smartsheet.com\/discussion\/106270\/completely-stumped-on-this-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106270\/completely-stumped-on-this-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106270,"commentID":379927,"name":"Re: Completely stumped on this formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379927#Comment_379927","dateInserted":"2023-06-09T19:58:02+00:00","insertUserID":151459,"insertUser":{"userID":151459,"name":"Paige_Hamby","url":"https:\/\/community.smartsheet.com\/profile\/Paige_Hamby","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/n7ZG0TBD44T33.png","dateLastActive":"2023-06-09T19:56:02+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-09T19:58:20+00:00","dateAnswered":"2023-06-09T19:39:44+00:00","acceptedAnswers":[{"commentID":379924,"body":"

@Paige_Hamby<\/a> <\/p>

I will admit, I was \"Completely Stumped\" for a moment as well... but I believe this should work.<\/p>

=IF(COUNTIFS({Customer Name}, Customer@row, {Document Type}, \"Upsell\", {Item Purchase}, HAS(@cell, \"SummitIT Upsell\")) > 0, \"Upsell\", \"//www.santa-greenland.com/community/discussion/5075/\")<\/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"}]}],"title":"Trending Posts","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending Posts