Best Of
Re: Formula to Capture Current Task Start Date and Next Task
[Current Task Start Date] should be doable with a standard INDEX/MATCH when matching on the Current Task.
[Next Task.] would require a helper checkbox column with the following column formula:
=IF(COUNT(CHILDREN([Task Name]@row)) = 0, 1)
Then in the [Next Task.] field you could use something along the lines of
=INDEX(COLLECT([Task Name]:[Task Name], Helper:Helper, @cell = 1, Status:Status, @cell <> "Complete"), 2)
Paul Newcome
Re: Trouble with Checkboxes
@KPH I think I figured it out. The user who shared the sheet with me has a pro account. I'm not sure why I can't edit it because I have an enterprise account. I think the user sharing the sheet needs to update to a business plan
Kayla Q
Re: formula works fine in individual cells but can't be converted into a column formula
Hi @OshaK
I hope you're well and safe!
To add to Carson's excellent advice/answer.
As a workaround, you can also use the INDEX function to reference a specific row and still be able to convert it to a column formula.
Would that work/help?
I hope that helps!
Be safe, and have a fantastic week!
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å
Re: IF formula to calculate points
Hi @Sam C
I hope you're well and safe!
Try something like this.
=IF(Occurrence@row = "Tardy", 1, IF(Occurrence@row = "Late", 2, IF(Occurrence@row = "Half", 3, IF(Occurrence@row = "Full", 5,0)
Did that work/help?
I hope that helps!
Be safe, and have a fantastic week!
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å
Connect and hear from past ENGAGE attendees 👋
Hello Community,
It was back in September at ENGAGE in Seattle that we announced ENGAGE will be coming to London May 14-15, 2024!
If you’ve been to ENGAGE previously, help our first-timers prepare for the upcoming ENGAGE in London by sharing your experience. [Earlier this year, many of you shared what you were looking forward to most at ENGAGE and we loved reading your responses.]
What would you say to someone who's considering attending ENGAGE for the first time?
If you’re going to be experiencing ENGAGE for the first time – connect with other attendees in the comments below, learn about the experience peers have had in the past, and get helpful advice.
ENGAGE in London will be an immersive experience packed with actionable sessions (presented entirely in English), real-time solutioning, thought-provoking speakers, and the tools to help you realise new potential.
Different from ENGAGE in Seattle, our London event will be much more intimate and ideal for connecting with like-minded people.
Be sure to check back regularly for updates. Event registration opens in February.
Hope to see you there.
Arsineh
Re: Connect and hear from past ENGAGE attendees 👋
What would you say to someone who's considering attending ENGAGE for the first time?
- Don't even hesitate, just GO! You won't regret it (except perhaps if you think back on previous ENGAGE years when you did not or were unable to attend).
- Bring your laptop as you will learn sooooo many new tips and tricks that you will want to nerd out back in your hotel room each night implementing everything you learned and updating your existing solutions.
- Drink lots of water as your mouth may go dry from talking and engaging with everyone. The networking is truly one of the most powerful and valuable reasons to attend ENGAGE!
- Explore all of the booths and be sure you have brought with you a bigger suitcase to store all of the goodies you will pick up along the way.
- Prepare to be energized by everyone around you, being with like-minded thinkers and fellow Smartsheet lovers will get you hooked and then you will never want to miss out on another ENGAGE, ever!
- Plan on attending the gatherings and parties - they are amazing!
- Enjoy, enjoy, enjoy --- ENGAGE is one of the best experiences!!!!
Re: Connect and hear from past ENGAGE attendees 👋
I had to pitch going to Engage and had been turned down in the past. After going once, I don't feel like this can be missed. It was SUCH an amazing experience. As someone who felt strongly about my skill level in core product, I was BLOWN AWAY at the quality of the Engage program.
Key notes are amazing, dynamic and valuable.
The sessions are valuable and really do unlock new potential.
The diversity and inclusion - there truly is a place for everyone. Even meeting new people at meals and learning about their skills.
The "hall" - I was able to get expert access to solve an ongoing issue I was experience.
The meeting for me was an opportunity to dream in color, and I was able to bring back actionable insights to bring us to the next level. Out of every professional conference that I have attended, this was by far the best!
Re: Help with formula for Current, Next, Last week calc based on date
The syntax for the nested IF would be
=IF( [Due Date] occurs during this week , "This Week",
IF( [Due Date] occurs next week , "Next Week",
IF( [Due Date] occurs last week , "Last Week",
IF( [Due Date] occurs after next week , "After Next Week ",
IF( [Due Date] occurs before last week , "Before Last Week")))))
Each elseif is a new IF(
There is a comma to separate the logic and the result if true
Then another comma to separate the result if true and the result if false (which in your case is another IF)
You need to replace the part in bold with your logic to determine what constitutes "this week", etc.
KPH
Re: Adding Time to a date column to trigger an auto email notification.
Hi @Robert S
If you're looking into another sheet, we'll need to use a Cross Sheet formula to return the number in your Bake Time column.
Assuming that the PartNumber is unique, we can use an INDEX(MATCH combination formula to look into your second sheet and bring back the time associated with the matching PartNumber. See: Formula combinations for cross sheet references
So something like this:
=INDEX({Bake Time Column}, MATCH([Part Number]@row, {Part Number Column}, 0))
Then we can use this in your IF statement to say that if there's a date, add the days that the cross-sheet formula returns:
=IF(ISDATE([Date In]@row), [Date In]@row + IFERROR(INDEX({Bake Time Column}, MATCH([Part Number]@row, {Part Number Column}, 0)), " - No Match"))
Let me know if that makes sense and works for you!
Cheers,
Genevieve
Genevieve P.
Re: Calculate count based on certain criteria.
Thank you all for the help on this, we ended up with the following formula:
=IF(OR(Sit@row = "NA", Sit@row = "", Removed@row = 1), 0, COUNTIFS([School Name]:[School Name], [School Name]@row, Sit:Sit, Sit@row, Removed:Removed, Removed@row = 0))
Adding the OR statement so if it was NA or <Blank> would force the 0.
Kevin7859