Best Of
Re: Combining If and AND statements
It is basically just stringing the cell data together.
The only part that wasn't just "adding" the cells together (with spaces in between) is the middle portion where we used the LEFT function to pull the leftmost word out of the cell for the size, and we used the FIND function to tell it how many characters to pull from the LEFT going up to the first space.
Paul Newcome
Re: How to countifs "<1"
Ok. This may be it. You have two different data types in the same column, (text and numerical), and most functions don't play well with that.
Insert another column on the source sheet (it can be hidden after setup) and use this:
=[Column Name]@row + ""
(Plus double quotes)
Then try your COUNTIFS referencing this new helper column.
=COUNTIFS({Position Title}, @cell = $[Position Titles]@row, {Helper Column}, @cell ="<1")
Note: You may also need to put quotes around the numbers in the other COUNTIFS once you adjust them to also look at the helper column.
Paul Newcome
Re: data shuttle filter lacks an option to filter rows dynamically on date fields
@Naren, while you will need an extra column its easy to accomplish.
- Create a new column called say "Today"
- In the column, create a column formula, for TRUE / FALSE, to check if the date in JVd Date = Today().
- This way you will get TRUE for columns where JVd Date is today.
Now as this represents a static column, you can use filter on this column in your data shuttle. Also, you can hide this column in sheets so as not to add clutter. Hope it helps. Let me know if you need any further clarity on above steps.
Re: There are too many cells referenced by formulas in this sheet
Did you try replacing the vlookup formula with index/match? It is known to be more efficient than a vlookup.
JKHALID
Re: Constraint Types in Gantt View
The lack of this functionality is causing our Project management team to not use Smartsheet as their primary platform. This is a must-have functionality.
Re: Calculating the difference in hours:minutes between 2 dates/times
Below is a link to a thread that deals with time based calculations in Smartsheet.
Depending on when exactly you need this though, there may be an announcement next week at the ENGAGE event regarding time calculations using a dedicated function. 🤞 I personally would wait (if you can) to see what new features get rolled out next week and then explore your options.
If you need it right now though...
Paul Newcome
Re: Please let Smartsheet work with time
I add my request for this - as well - for Time and Timezone capabilities (esp the ability to allow a user to see what date/time a task would fall on in their timezone!). This is the one feature that makes it difficult (or impossible) to use SmartSheet for our use cases. At this point, we'll have to go back to Google Spreadsheets and individual columns for each user until the ability to work with time and set timezones for various items.
Why is this is a critical need for us?
- We schedule events and projects across multiple Geographies.
- We need to have setup and teardown precisely coordinated between individuals in different regions.
- We need to ensure that any user who has to cover for that individual (might be in a different country) also knows precisely when that task is due in their timezone).
- Missing timezones and building our widgets too early or too late either adds significant, wasted cost to our work, or it causes our customers/users to not have the resources necessary
I note that Start and Finish each have a type of "Date/Time" - so the timezone information must be encoded in the sheet for Time to exist here. However, I can create no column called "Time", "Date/Time", or "Timezone" nor any equivalent variable for such an operation.
Add to this that the Smartsheet FAQ states that the last editor of a sheet changes the sheet's source timezone to their's - this is problematic with PM's across different geographies are concurrently updating a plan and it alters everything about an event or project that requires cross-TZ-coordination.
Re: Formula for multiple Criteria
Hi @BonW,
Try this.
=IF(OR(CONTAINS("Invoice", ColumnB@row), LEFT(ColumnC@row, 1) = 1, LEFT(ColumnC@row, 1) = 2, LEFT(ColumnC@row, 1) = 3), "Ready")
Hope that helps,
Dave
DKazatsky2
Re: Update a cell value within an automated workflow
Hi @Naren
I hope you're well and safe!
You could use so-called helper columns: one for the negative value, one with a formula showing the changed value if something else is true, and then a formula in the main column to show the correct value in each step.
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: Counting Cells that contain text
Hi @Blake Mears
I hope you're well and safe!
Try something like this.
=COUNTIF(author:author, CONTAINS(1385,@cell)
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å