Best Of
Re: Hello! What is the formula to calculate percent complete for a task?
Hey @Kariv
Depending on what it is you want to do, you could potentially use the AVGW Function to add a weight.
With AVG or AVGW, you could use the Collect function to filter down your ranges. Here's an example!
Here are other Community posts that have more information:
- Manual % complete formula in the parent row
- Formula for parent row that calculates % of child rows, based upon date durations
Genevieve P.
Re: Redirect to URL based on form field entry
@Paul DG I believe you could hide everything except the field that determines the logic, and the header if you wanted. You just have to get the logic right based on your criteria. But you are correct there is no auto-redirect, they would still have to click the link.
Example: https://app.smartsheet.com/b/form/a6d1d0f4733c4e8791c33397fc8a54d4
Samuel Mueller
Re: Predict or set which row a form writes to
Hi @Freymish
I don't have the answer to your formula question. However, you might not need it. The form should populate the new row directly below the last used row in your sheet. I've only had issues like you describe, where 10 blank rows are between entries, if I've been in the sheet and touched a cell in the 10 blank rows that are always there at the very end. It is as if Smartsheet recognizes them as "used" and then skips over them and puts the new data below. If you don't click in them at all, you could be fine. 🤞
KPH
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