Best Of
Re: COUNTIFS with multiple criteria
Hi @Btrombler,
[Submission Date:Submission Date] should be written as [Submission Date]:[Submission Date]
Hope this helps,
Dave
DKazatsky2
Re: Calculate respective years based on start date and term
Hi!
So I would approach it like this… This assumes your Term in Months is always divisable by 12. I could adjust for half years as well if you need.
YEAR 1
=if([Term in Months]@row/12>.99,1+YEAR([Start Date]@row,"")
YEAR 2
=if([Term in Months]@row/12>1.99,1+[Year 1]@row,"")
YEAR 3
=if([Term in Months]@row/12>2.99,1+[Year 2]@row,"")
YEAR 4
=if([Term in Months]@row/12>3.99,1+[Year 3]@row,"")
YEAR 5
=if([Term in Months]@row/12>4.99,1+[Year 4]@row,"")
YEAR 6
=if([Term in Months]@row/12>5.99,1+[Year 5]@row,"")
You could extend this for as many years as you need by following the pattern in the logical statement portion of the IF.
Let me know if this does not work and/or if anyone has any other ideas!
NickStaffordPM
Re: NEW Core Product Certification by Smartsheet University
I think it would be amazing to have a practice test as many individuals with test anxiety especially when money is involved.
Cassi
Re: July Question of the Month - Join the conversation and receive a badge
I think the best mentors are the ones that embrace the ideas of empathy and "radical candor" - they know how to acknowledge things that are happening and hold you accountable for your role in stewarding your own life and career. They ask great "how might you…." and "what if you reframed it as….." questions to help shift you into new and different ways of thinking. In short, they don't solve things for you but they help you get to the answers yourself!
Shannon R.
Re: Conditional copying of rows from one sheet to another
Hi @mch_opc
I hope you're well and safe!
You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.
Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.
To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the Destination sheet.
Would any of those options 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: Using forms to update a sheet
A form submission creates a new row, so you'll need a different process to update an existing row. Depending on your data/permissions needs and your plan, you may be able to send an update request to those who are submitting additional information, use premium app Dynamic View to allow people to have the ability to update only specific fields, share the sheet, or create a report for updating (including some locked columns that don't require updating if that's the case) and embed in a WorkApp for people to use for updating (without sharing the underlying sheet). There are other possibilities I'm sure, but these are the ones I can think of.
Good luck!
Kerry St. Thomas
Re: Automation: 'Copy Rows to Another Sheet' doesn't really work as advertised.
Hello @Berto_DA
Others may have a more elegant solution, however, I have had a very similar situation. If I am understanding you correctly, you want to copy our the rows that were updated to another sheet, then you will clear all of the contents so the following week they can be populated again?
I suggest using a helper checkmark column in your sheet. The helper column would populate with a checkmark based on certain criteria — whatever criterion you are using to make the decision to include the rows when you run this workflow manually. So, build a column formula for the helper column. Then, your workflow condition would say HelperColumn is Checked. Only the rows with a check will be copied out.
Helper column formual would be something like — ("Criteria" can also be another column in that row, if you have a column to reference)
=IF([Column Name]@row = "Criteria", 1, 0), " ")
When it works on one row, right-click and select Convert to Column Formula — that way, after you clear the data, the formula will remain for the next round of updates.
I use a system similar to copy rows from sheets that are formula-driven, to capture the historical hard data each month at a specific point in time, and retain those values. It works really well.
I hope this was helpful.
Pauline
Pauline J
Re: The Overachiever guests are back! [RECAP] Celerion x Angie Badgett
Go Angie go Angie go!
Alison C.
The Overachiever guests are back! [RECAP] Celerion x Angie Badgett
Loved our previous episode featuring one of your Community peers? You’re in luck, because we’ve got another one! Celerion Executive Director @Angie Badgett, is one of our unstoppable Overachievers, and the latest guest of the Brand Collective Podcast.
Curious what this episode has in store for you?
- We explore key challenges and solutions when it comes to working with marketers and creatives in highly regulated industries.
- Didn’t expect a clinical research organization to be on TikTok? Angie explains why social and word-of-mouth marketing are instrumental to Celerion’s success.
- As a 19-year Celerion veteran, Angie discusses what it takes for a company to create a culture of employee loyalty and long-term satisfaction.
- We learn about the four levels of conversion that Angie and her team use to measure the impact of their marketing initiatives.
🎧 Choose one show about your industry — Do they portray it well?
Angie shared how she felt about the dramatic portrayal of a clinical research study patient on a medical series on Netflix. Have you had a similar experience? Pick a show about your own industry, and let us know how close it is to reality!
Want to check out more Podcast Recaps? Find them all here. ✨
Happy listening!
Rebeca S.
Re: Formula help combining and counting two conditional columns
Try:
=COUNTIFS({SheetA|Priority}, OR(@cell = "Highest", @cell = 5), {SheetA|Status}, OR(@cell = "New", @cell = "To Do")
Nic Larsen
