Best Of
Re: Dynamic dropdowns - Waterfall request
This would be incredibly helpful for us. I would be happy to just be able to reference a column in another sheet - just like you would do with a regular sheet reference to build out my applicable dropdown values. To have the ability to sort or not sort the list within the dropdown properties would be a 'nice to have'.
Jim B
Re: Hyperlink in Sheet is not accessible on the workapp
Hey @Danielle Maroon
You are correct, currently hyperlinks do not show in a Workapp. Please feel free to add your voice and vote to this Product Idea:
In the meantime, if you add the full URL instead of a hyperlink this will be clickable.
Cheers,
Genevieve
Genevieve P.
Re: Removing duplicate rows from a report
Hey @Sandy Glassberg
You can find the first instance of a row with the help of a column that indicates row position (ex: [Row ID], Created Date - anything that tracks the relative position down (or up) a sheet). I will assume that you add new rows to the bottom of the sheet AND once a row is created, you are not changing it's position on the sheet (sliding the row up or down the sheet to a new position).
If you do not have the system autonumber column or Created Date, please add one of these. You will need to SAVE the sheet after adding.
I'll assume you added the autonumber column. If you added/use Created, edit the formula to replace [Row ID] with Created. Also be sure to edit the formula to use your actual column name for your part number. The formula below will flag duplicates but will leave the first instance of a row unchecked (if you use =1 instead of greater than 1 it will check the first instance and leave the duplicates unchecked. If you have many duplicates it may be simpler to flag the first instance.)
=IF(COUNTIFS([Full description]:[Full description], [Full description]@row, [Row ID]:[Row ID], @cell<=[Row ID]@row)>1,1)
Filtering on this column (to either include or exclude, depending on if you flagged your duplicates or first instances) should give you the list you desire.
Does this work for you?
Kelly
Kelly Moore
Re: Cell Reference -1?
If your studies are taking the name from the parent (black coloured) rows, then you can delete the Row ID. You'll still need an extra column for the study name, but can use this instead:
=IF(COUNT(CHILDREN()) > 0, [Study Name (Parent)]@row, PARENT([Study Name (Parent)]@row))
Where the Study Name (Parent) would the value to modify and the Study Name would be what you can use to pivot in Excel. The middle column's function would be covered by your other columns in your sheet.
You won't be able to use the formula in the column itself as it would obviously cause a circular issue similar to yours, but this is a workaround to get the desired outcome.
Nick Korna
Re: Different Contacts for Final Signer in Docusign
If it helps at all, one of our solutions (which didn't go ahead, so I didn't get to fully test it) was to have the final signatory a generic company email. We would then log into DocuSign and forward/assign the document to the appropriate signatory, once we knew who the appropriate signatory would be (i.e., a field in Smartsheet updated, with an update request sent to those monitoring the generic email).
Jason Albrecht
Re: Moving Workspaces to an Archive
From my understanding, you should only be able to move workspace content - not the workspaces themselves into other workspaces. I would create a new folder in the archive workspace then move all of the content from the old workspace into the correct folder in the archived one.
Mike Wilday
Re: I would like to ask for help with updating status my formula:
The slight issue here is adding the 5th value as the symbols column doesn't support having 5 coloured circles unfortunately. You can get round this by adding either a symbol/emoji directly or using the UNICHAR function.
The other issue is that there aren't exact matches of the dots used in Smartsheet, so you will have to decide what works for you. For example, using a black dot instead of grey:
=IF(Status@row = "Upcoming", "Blue", (IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Requires Review", "⚫", (IF(Status@row = "Behind", "Red", "Green"))))))
Or with UNICODE:
=IF(Status@row = "Upcoming", "Blue", (IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Requires Review", UNICHAR(9899), (IF(Status@row = "Behind", "Red", "Green"))))))
However the extra one doesn't quite fit in with the others (slightly different size/position in the cell), but if needs be you can make them match more stylistically by using other symbols from the UNICHAR set.
For example:
=IF(Status@row = "Upcoming", UNICHAR(128309), (IF(Status@row = "In Progress", UNICHAR(128993), IF(Status@row = "Requires Review", UNICHAR(9899), (IF(Status@row = "Behind", UNICHAR(128308), UNICHAR(128994)))))))
Another alternative is to use the black dot (code 11044) and alter the font colour & size to get it 'close enough', but this also has the side effect of making your rows slightly larger (due to the increased font size of the cell). For example:
Is using the formula:
=IF(Status@row = "Upcoming", "Blue", (IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Requires Review", UNICHAR(11044), (IF(Status@row = "Behind", "Red", "Green"))))))
With the font colour set to the darkest grey in the column and font size 12.
Pick whichever option works for you! 😀
Nick Korna
Re: I would like to ask for help with updating status my formula:
This should do what you're after (assuming you're happy with what you currently have already):
=IF([% complete]@row = 0, IF([Start Date]@row < TODAY(), "Behind", "Upcoming"), IF([% complete]@row = 1, "Complete", IF(AND([% complete]@row = 0.7, [Start Date]@row > TODAY()), "Requires Review", IF([End Date]@row < TODAY(), "Behind", "In Progress"))))
Note that this only works for exactly 70% completion, but you can easily alter it to equal to & greater than by adding a single symbol:
=IF([% complete]@row = 0, IF([Start Date]@row < TODAY(), "Behind", "Upcoming"), IF([% complete]@row = 1, "Complete", IF(AND([% complete]@row >= 0.7, [Start Date]@row > TODAY()), "Requires Review", IF([End Date]@row < TODAY(), "Behind", "In Progress"))))
Hope this helps, post if you've any problems/queries on it.
Nick Korna
Re: Increase the Latest Comment Column Character Limit
We're using the Latest Comments field in a customer-facing project artifact, and it would be so helpful to have this limit increased!
Rather than providing customers access to edit our sheet (and view internal-only items), I'm notifying them when certain triggers occur on the sheet. Latest Comment is one of the most important fields I include in the body of those notification emails. I'm trying to train my team, but currently, customers are receiving notification emails where the Latest Comments cut off, and it causes a great deal of confusion.
Please consider this improvement to a great feature!
Amy.Mizzi.RP
Re: Using full stops or commas for numbers
You can't force the user to use commas vs periods in the form directly, but what you can do is insert a helper column on the sheet and use a formula to swap any periods out for commas and convert it into a numerical value.
=VALUE(SUBSTITUTE(Spesa@row; "."; ","))
Paul Newcome