Best Of
Re: Find first blank in other column and return value in same column
Hey Laura,
This is pretty interesting. I was able to do this but I don't know how pretty it is. Here is the example sheet:
So here is what's happening,
- Helper Column added to identify first blank date cell in a given range of cells. You have to apply/customize this formula to the range of children cells you are applying it to.
- Formula in parent Milestone cell that looks at the helper column and collects the names of the Milestones if they are checked.
Here are the formulas from the sheet, but they make more sense in context,
=IF(COUNTIF(Date$2:Date@row, ISBLANK(@cell)) <> 1, 0, 1)
=JOIN(COLLECT(Milestone2:Milestone9, [Column2]2:[Column2]9, 1), ",")
Please let me know if that works for you!
Re: Workflows not working right away?
How soon is "Right away"?
I've found with some recent workflows I setup to notify to a Slack channel that it generally takes 1-3 minutes for the alert to generate.
Re: CountIF for a multi-select drop-down
Hi @Sarah Bird
You're gonna need to use either FIND, HAS or CONTAINS here.
HAS is designed to work in Muti-Select dropdown columns so it's the best choice here:
=COUNTIF(Comments:Comments, HAS(Comments@row, "Requirement A"))
Others will be alike:
=COUNTIF(Comments:Comments, FIND(Comments@row, "Requirement A"))
=COUNTIF(Comments:Comments, CONTAINS("Requirement A",Comments@row))
CONTAINS and FIND will look for strings of characters so both should work as well, HAS will specificly look for the item "Requirement A"
Hope it helped!
Re: CountIF for a multi-select drop-down
The "@row" is replacing the row number and refers to the actual row. It's better to use @row that using numbers as a general rule, except if you want to point to a specific cell. In which case it's better to use the "$" argument prior to the row number to make sure it doesn't change when dragging down the formula or whatever.
such as:
=FIND(Comments$3, "Requirement A")
That one would check if "Requirement A" is present in cell row 3 of the Comments column.
Hope it helped!
Re: How do I calculate the number of answers based on a specific value
=COUNTIFS([Column1], "Male", [Column2], "Strongly Agree")
Re: INDEX and MATCH help
Hi @GHobbs
Try this:
=INDEX({StartDateColumnref}, MATCH([Task Name]@row, {TaskNameColumn},0))
#INVALID COLUMN VALUE probably means you're trying to return a Date to a non Date column.
Hope it helped!
Re: Is there a way of using QR codes to generate forms without having to download Smartsheet app?
For me, in private/incognito mode, the form showed up in the browser, and then a banner shows that I can open it in the Smartsheet APP.
On my daughters iPhone that doesn't have the app installed, it only showed the form, but that can be a setting on her phone, so the app banner doesn't appear.
Re: Formula to generate the expiration date, 1 year after date signed?
In this example: Two columns Date1 and Date2
Use this formula in Date2 column:
=DATE(YEAR([Date1]@row) + 1, MONTH(([Date1]@row), DAY([Date1]@row)))
I hope this helps.
Ramzi
Re: Can a large number of deletions (cell) be undone at once, say 1800?
Hi @Teresa Neal
To add to Ramzi's excellent advice/answer,
To maybe make it simpler to add the information back, you can export the Activity Log.
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 help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
Re: Looks & format of automated emails
Hi @Abnoos Moslehi and @Tim Meeks
We recently released an update where you can now choose to only send the message from your automated alert without providing a link to the source sheet in the email. Our Release Notes page has more information, see here!
Keep in mind that if you want to send a message without a sheet link, but you also want some of the row content included in that message, you can add this dynamic data into your message by using specific formatting in your text (see our Help Center article on this, here.)
Cheers!
Genevieve