Best Of
Re: How to combine two rows of non-numerical Data?
Have you tried adding in a space when adding the two names together?
=First@row + " " + Last@row
Paul Newcome
Re: how to sort filtered data?
Creating a report for each time you want to filter a sheet is pretty inefficient. Filtering and then sorting is available in Excel (even the online version) and MS Project (also the less robust online version) so seems like something most users are accustomed to doing. Hopefully this is coming soon!
Re: COUNTA alternative in Smartsheet
You are dead on with the concept... you only missed it with parentheses.
=IF(SUM([AR]@row:[AT]@row) > 0, IF(OR(ISBLANK([AU]@row), ISBLANK([AV]@row)), "Please enter review reason and detailed explanation", IF( COUNTIFS([AR]@row:[AT]@row, NOT(ISBLANK(@cell))) > 1, "Please enter one type of increase recommendation only", IF(NOT(ISBLANK([AW]@row)), [AW]@row / [V]@row))), 0)
Carson Penticuff
Re: COUNTA alternative in Smartsheet
You are correct in that COUNTA is not present in Smartsheet. You can use COUNTIF, as you mentioned, though I prefer to use COUNTIFS instead. There is also no IFS, so to cover multiple cases, we must nest several IF statements. Will you be setting these up as column formulas? If so, here is the equivalent of the first formula. Since Smartsheet references column names instead of alphabetic references, you will have to replace the column references with your actual column names.
=IF(SUM([AR]@row:[AT]@row) > 0, IF(OR(ISBLANK([AU]@row), ISBLANK([AV]@row)), "Please enter review reason and detailed explanation", IF(COUNTIFS([AR]@row:[AT]@row, NOT(ISBLANK(@cell))) > 1, "Please enter one type of increase recommendation only", IF(NOT(ISBLANK([AR]@row)), ROUND([AR]@row * [V]@row, 0), IF(NOT(ISBLANK([AS]@row)), ROUND([AS]@row * [U]@row, 0), IF(NOT(ISBLANK([AT]@row)), ROUND(([AT]@row - [AL]@row) * [U]@row, 0)))))), 0)
Carson Penticuff
Re: Rich Text formatting in cells
Adding my voice to this ask.
We would like to use Smartsheet to track written copy content for our projects, but the inability to have different formats of text within cells is a huge limiting factor.
We would like to put all text for a topic in a single cell. This includes titles, paragraphs, quotes, key words, etc. and requires at minimum the support of selective bold, italics, bold italics, bullet points and numbered lists. Support for multiple font sizes and selective font colour would also be an asset. We would be able to copy the text directly out of the cell and paste into another program (typically Adobe Illustrator or Indesign) to create our graphic panels.
In our use case, each text piece is assigned a tracking number in a different cell of the same row to that all our content pieces can be sorted or filtered as needed. We are typically working with a hundred or so pieces of text that are each 1-2 paragraphs in length, so a database/spreadsheet system gives us the best overview and control vs. one large word processing document (cannot sort content) or multiple small word processing documents (cannot see full picture of the project). Bonus points if another cell on the same row could house all the related images/graphics that go with the content, but that is a different ask.
LinetteOrigin
Re: Rich Text formatting in cells
I am not sure if there is another request or mention of this elsewhere but this suggestion is not only extremely relevant to text in cells but also in the comments!! Rich text formatting would be extremely helpful both in cells and in comments as there are no other real tools for note-taking in Smartsheet.
I know this request is on the roadmap, glad to hear it and looking forward to what comes of it!
jmillercvlav
Re: Formula - Helper Column Flag if Duplicate
Hi @Estephania,
The original formula provided by @Andrée Starå works with the addition of a "=" in the COUNTIF condition.
=IF([Employee ID]@row <> "", IF(COUNTIF([Employee ID]:[Employee ID], =[Employee ID]@row) > 1, 1))
FYI...the "=" is also needed if you use the formula provided by @Eric Law (at least in my testing).
=IF(COUNTIF([Employee ID]:[Employee ID], =[Employee ID]@row) > 1, 1, 0)
Hope this helps,
Dave
DKazatsky2
Re: Formula - Helper Column Flag if Duplicate
Hi @Estephania
I hope you're well and safe!
Here's an example of how it can be structured. This will show all duplicates (the original and the duplicate(s)).
Add the formula to a so-called helper column (Checkbox), and update the column name to match yours.
=IF(ColumnName@row <> "", IF(COUNTIF(ColumnName:ColumnName, ColumnName@row) > 1, 1))
Would that work/help?
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å
Re: countifs and a date that falls between two dates
The first formula is missing a comma before "<=TODAY())"
John C Murray
Re: Share Data Shuttle Workflows
@Kleerfyre That's a good point! It's not as straightforward as it could be, but if I look at a sheet's activity log and see that a coworker has modified a dropdown list at 2am every day, that's a pretty good sign that they are updating it via Data Shuttle.
Julie Fortney