Best Of
Re: I am trying to do multiple if statements in a formula but can't seem to get it right!
Try this and be sure that your column types are Dates:
=IF([Assessment Date Booked]@row > [Deadline 2]@row, "N/A", IF([Assessment Date Booked]@row < [Deadline 1]@row, [Assessment Date Booked]@row + 730, IF([Assessment Date Booked]@row < [Deadline 2]@row, [Assessment Date Booked]@row + 365, "")))
This revised formula checks:
- If
[Assessment Date Booked]@rowis greater than[Deadline 2]@row, it returns "N/A". - If
[Assessment Date Booked]@rowis less than[Deadline 1]@row, it adds 730 days to[Assessment Date Booked]@row. - If
[Assessment Date Booked]@rowis less than[Deadline 2]@rowbut not less than[Deadline 1]@row, it adds 365 days to[Assessment Date Booked]@row.
If none of these conditions are met, it returns an empty string by default.
Shoutout to ChaptGPT for the assist~
AaronAtWork
Re: I am trying to do multiple if statements in a formula but can't seem to get it right!
Hey @ionam,
Looks like you removed the wrong character here:
"Assessment Date Booked]@row+730
Remove the " at the beginning and put the [ in its place.
bisaacs
Re: Why does the RM view not show people who are assigned to the project?
Hello @Will Jeffords !
Thank you for your help! I have a couple answers/questions based on your kind assistamce:
- The email address we use for Tuhin is the same structure we use for everyone…name@ssk.com. We don't have any other structure and it's interesting to hear you suggest there are different structures for Smartsheet and RM. So, since we use the same structure for everyone, we should be getting the same message for other, but, we're not. However, it's good to know there could be a deeper wrinkle here.
- Magically, RM has recognized that there are people on the project…so, whatever it was it was either a time delay or who knows what, but I didn't so anything other than copy the project to mess with it and poof, things started working (the next day)
- We did have people assigned, I double checked where you kindly suggested and they are in there…so, maybe the magical fix that made it work also populated that.
Most importantly, THANK YOU!!!!!!!! Keep up the great work sir.
Jeff
Jeff Casto
Re: Community improvement - get alerts on conversations you participate in
Hi @Jgorsich
The types of notifications you receive are up to you! 🙂 In your Profile, click Edit Profile to adjust your Notification Settings. From here you can choose what types of alerts go to your email or just appear here in the Community.
You can turn on email alerts for conversations you have participated in, even if you're not @mentioned:
Cheers,
Genevieve
Genevieve P.
Re: How would you extract a number from a cell that contains text and numbers?
Hi @Jerell Parker ,
There are several ways to do this. If the number is always the same number of characters and in the same position you can do:
=VALUE(LEFT([text-num]@row, 6)) where [text-num] is the name of your column with mixed entry.
If the numbers are always at the beginning of the string but their length varies you can do:
=VALUE(LEFT([text-num]@row, FIND(" ", [text-num]@row)-1))
Help?
Mark
Mark Cronk
Re: Nominate Peak Humans & get a badge!
Of course @Genevieve P. and @Paul Newcome both are gems.
Peter South
Re: Heatmap in Resource Management
@Genevieve P. I have been working with it for a couple of days now. It is starting to look like a heat map. It does require some cleanup on our side. I am still trying to understand the filtering, as not all resources or projects show up; it is the way we set up projects and our laziness on our side because not every project is "filled out" as it should be. Overall, what I have seen will be very useful.
There are some things I would change or like to see added, and I will enter those into the system
Again, thank you for your help.
MilesinAtl
Re: How to identify row number in a sheet where new rows will be added
The above does work as long as the new row inserted has at least two rows with the formula in it above and/or below it. The built in auto-fill feature should pull it in.
A column formula version requires an auto-number column (called "Auto" in this example) and this would go inn the column that you want to hours the row number in:
=MATCH(Auto@row, Auto:Auto, 0)
Paul Newcome
Re: Is it possible to evaluate multiple predecessors and match to a cell value in another column?
Just an update on this, @Jason Tarpinian solution works great. We had a need to expand that to up to 10 predecessors. In order to do that, I had to add a comma as a delimiter between values and at the end of the final value. Then used the following formulas to extract the individual values (Used a column called Constraints for the entry of the multiple predecessor values):
Contraint 1: =IFERROR(LEFT(Constraints@row, FIND(",", Constraints@row) - 1), Constraints@row)
Constraint 2: =IFERROR(MID(Constraints@row, (FIND([Constraint1]@row, Constraints@row) + LEN([Constraint1]@row) + 1), (FIND(",", Constraints@row, (FIND(", ", Constraints@row, (FIND([Constraint1]@row, Constraints@row))) + 1))) - FIND(", ", Constraints@row, (FIND([Constraint1]@row, Constraints@row))) - 1), "")
Constraint 3: =IFERROR(MID(Constraints@row, IF(LEN([Constraint2]@row) <> 0, IF(LEN([Constraint2]@row) <> 0, FIND([Constraint2]@row, Constraints@row) + LEN([Constraint2]@row) + 1)), (FIND(",", Constraints@row, IF(LEN([Constraint2]@row) <> 0, (FIND([Constraint2]@row, Constraints@row) + LEN([Constraint2]@row) + 1))) - (IF(LEN([Constraint2]@row) <> 0, FIND([Constraint2]@row, Constraints@row) + LEN([Constraint2]@row) + 1)))), "")
Constraint 4: =IFERROR(MID(Constraints@row, IF(LEN([Constraint3]@row) <> 0, IF(LEN([Constraint3]@row) <> 0, FIND([Constraint3]@row, Constraints@row) + LEN([Constraint3]@row) + 1)), (FIND(",", Constraints@row, IF(LEN([Constraint3]@row) <> 0, (FIND([Constraint3]@row, Constraints@row) + LEN([Constraint3]@row) + 1))) - (IF(LEN([Constraint3]@row) <> 0, FIND([Constraint3]@row, Constraints@row) + LEN([Constraint3]@row) + 1)))), "")
For additional constraints, the formula for constraints 3 & 4 can be copied and modified. Definitely not a pretty solution, but it is working very effectively.
Hope this is helpful to others, have a great day!
TomG
TomG
Re: View activity of a certain user for a time period?
Hi,
I hope you're well and safe!
There is a premium add-on called Event Reporting that might be helpful. Do you have access to it?
I hope that helps!
Be safe, and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅ Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
Andrée Starå
