Best Of
Re: Join the conversation on STEM education and help nurture the next generation of visionaries
One of the women in STEM that inspires me is Emily Calandrelli. She is a fellow WVU alum! Emily has done some great work for NASA, but is primarily a science communicator and educator. In particular, she created and filmed an educational science show, Emily's Wonder Lab, while pregnant. Providing this representation is SO important for kids and adults alike, and she's a great role model for girls wanting to get into STEM.
Re: Copy Parent and Child Rows
Agreed, painful. It is bewildering to me that after all this time—I've been a faithful Smartsheet user for going on a decade now—some just really basic user-oriented stuff like this has just been ignored. Laurels/resting...the surest way for software to die.
Suzanne Raphael
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.