Best Of
Re: How to address column restriction error and value does not conform to req for PICKLIST?
Not sure why Smartsheets all of a sudden had issues, but the problems were associated with date and drop down columns being restricted to dates only, and only drop down items. Had to turn all of them off. Seems to be working so far.
jshrum
Re: Date Closest to Today
Hey @A Rose
In this instance we'll want an entirely new formula.
We can use ABS to return the absolute value of a NETDAYS formula (meaning if it's negative, it will make the number positive, so we can compare Future and Past dates all together).
Here's the Nested IF statement I would use in 1 column (no helpers needed):
=IF(AND(ABS(NETDAYS([Date1]@row, TODAY())) <= ABS(NETDAYS([Date2]@row, TODAY())), ABS(NETDAYS([Date1]@row, TODAY())) < ABS(NETDAYS([Date3]@row, TODAY()))), [Date1]@row, IF(AND(ABS(NETDAYS([Date2]@row, TODAY())) <= ABS(NETDAYS([Date1]@row, TODAY())), ABS(NETDAYS([Date2]@row, TODAY())) < ABS(NETDAYS([Date3]@row, TODAY()))), [Date2]@row, [Date3]@row))
=IF(AND( ABS(NETDAYS([Date1]@row, TODAY())) <= ABS(NETDAYS([Date2]@row, TODAY())), ABS(NETDAYS([Date1]@row, TODAY())) < ABS(NETDAYS([Date3]@row, TODAY()))), [Date1]@row,
If the days between Date1 - Today are less than the days between Date 2 & Today AND less than Date 3 and Today, return Date 1.
IF(AND( ABS(NETDAYS([Date2]@row, TODAY())) <= ABS(NETDAYS([Date1]@row, TODAY())), ABS(NETDAYS([Date2]@row, TODAY())) < ABS(NETDAYS([Date3]@row, TODAY()))), [Date2]@row,
OTHERWISE, If the days between Date2 - Today are less than the days between Date 1 & Today AND less than Date 3 and Today, return Date 1.
[Date3]@row))
Otherwise, Date 3 must be less than the other two, so return Date 3.
Cheers,
Genevieve
Genevieve P.
Re: Hello! What is the formula to calculate percent complete for a task?
Hey @Kariv
The reason you're seeing a different number is because when you use AVERAGEIF it will give you an overall average, looking directly at each number. However Project sheets using Dependencies will calculate a % Complete on Parent rows based on a weighted average, using the Duration of the Child Rows to determine the percent.
Here's an example I used in a different post to help clarify!
Genevieve P.
Re: Formula to get reduced value
I would set up the sheet with some extra columns to hold the running balance of each license and use IF formula to pull the correct running balance into the Balance license value column. Like this:
(you can hide the columns in grey so they aren't visible)
Row 1 is fixed with your starter license values. The formula are as follows:
In Balance license value enter this in row 2 and drag it down:
=IF(License@row = "A", A@row, IF(License@row = "B", B@row, ""))
I have written this as a nested IF so you can expand for licenses C, D, and E, as follows:
=IF(License@row = "A", A@row, IF(License@row = "B", B@row, IF(License@row = "C", C@row, IF(License@row = "D", D@row, IF(License@row = "E", E@row, "")))))
In A enter this in row 2 and drag it down:
=IF(License@row = "A", A1 - [Debit value]@row, A1)
And, similarly, in B enter this in row 2 and drag it down:
=IF(License@row = "B", B1 - [Debit value]@row, B1)
(when you add C, D, E, columns you can use the same replacing the text in bold with C, D, and E as appropriate.
=IF(License@row = "B", B1 - [Debit value]@row, B1))
KPH
Re: DV Issues
Hi @maltaee
Support received a number of reports of the same thing, where users cannot view or add comments if attachments are not displayed in the View Display settings.
We're currently investigating this, but in the meantime, you can have Comments appear again by adding in Attachments to the View while we wait for a resolution.
Thank you!
Genevieve
Genevieve P.
Re: Sorting not working properly
I had a sheet that would not sort correctly, and noticed a tiny message when I tried to sort telling me that it would sort like the drop down values were entered.
The lines I added had values that were not in the drop down list, and once I added them (in the order I wanted, in this case, alpha order), it sorted properly.
Sally Chavez
Re: How to transfer information to power bi on a hierarchical level?
Hi @Rachel r
I see that you're trying to identify levels of hierarchy, parent and child relationships from your sheet within Power BI. Happy to help with this.
There isn't current functionality to automatically identify hierarchical levels of parent or child, other than viewing the nesting within your sheet. Feel free to reach out to our Product Team to pass along this great suggestion by filling out your request here:
You could potentially achieve the desired effect however by creating a few additional helper columns that use hierarchical formulas to indicate nesting levels and parent rows. As an example of this you could create a column with Parent rows checked. Steps:
Create a Checkbox type column in your sheet titled "Check if Parent". Within row 1, use the formula, =IF(COUNT(CHILDREN()) > 0, 1, 0) and copy this down the column. If rows contain child rows beneath them, boxes will be checked indicating this:
You could also create another helper column to return the level of nesting for each row. Steps:
Create a Text Number type column in your sheet titled, "Nesting Level". Within row 1, use the formula, =COUNT(ANCESTORS()) and copy this down the column. This will return the hierarchical level for each row:
Additional guidance and information on using Hierarchy formulas can be found here:
Have a wonderful day!
Kind regards,
Eric
Eric D
Re: IF, AND, OR Functions
Hi dana,
If you're getting an #UNPARSEABLE error on your second formula, it's likely due to the mixture of straight quotes vs curly quotes. Smartsheet can only understand straight quotes in formulas.
I'd recommend deleting those curly quotes (in the community they appear slanted in your formula) and manually typing the quotes again. Refrain from copying and pasting your formula from another source, including the community.
Aside from the quotes issue, the syntax of your formula appears correct. (Although I don't know the names of the columns you have in your sheet.)
If you still receive an error, please let me know the specific error you're getting or odd behavior, and I can advise further.
Shaine Greenwood
Re: Countifs using TODAY()
Hi Sabrina,
Looks like there are two issues with this current formula. It's short one parenthesis at the end of the formula and the and function is incorrect. Try this!
=COUNTIFS(AND({LMS Lead},”Sabrina McDonald”, {Project End Date}>=TODAY()))
Here's a resource on the AND function, both of the values that need to be true for the formula to work need to both be in the and function. It's contradictory to how you would think it would work if you are writing a sentence but makes sense if you think of it as its own function.
Also just a note on the Today function, that function only updates today's date when you go into the sheet and save it or make changes. So a tip I got at Engage would be for you to set up an automated workflow to update a column with the date that you can reference instead (you can hide this row). That way the information you are looking for will always be up to date with today's current date if you want this to stay up to date in the background when you aren't using the sheet. Here's a screenshot of what that Automation could look like,
So if you were to use this way of recording the date within a [Today's Date] Column or TODAY column your formula would look like this.
=COUNTIFS(AND({LMS Lead},”Sabrina McDonald”, {Project End Date}>=[Today's Date]))
Matthew L
Re: Filter rows under parent row?
Hi Christian!
I'm interested to see what others have to say but AFAIK you cannot automate any kind of row movement within a sheet, other than having new rows be added to either the top or the bottom. Even if you move a row to another sheet and back again, it would still just populate at the top or the bottom.
You could have new rows come in at the top and they would be outside of your hierarchy system altogether as their own parent rows... you could have prioritization decided for you based on formulas or have the form submitter elect a priority from a drop down and then move the row under the appropriate parent manually after the fact.
Or find new ways to organize your prioritizations... You could sort by priority instead of dropping them into hierarchy. For example in the dropdown menu scenario you sort off of that column. The issue here is Smartsheet will not continually or automatically sort. You would need to re-sort whenever rows are added.
You could add filters to your sheet, one for each priority designation, and cycle through them.
You could create a report off of this sheet that "groups" the different items based on their priority. You can work in reports and any changes made are reflected in the actual sheet as well.
You could use conditional formatting to highlight rows a certain color based on the priority.
None of these options really solve your problem directly but hopefully it gives you some ideas.
Cheers
Josh Reed