Best Of
NEW! Your survival guide to formulas is here. Meet the Formula Handbook.
Hi Community,
Whether you’re summing child rows, calculating budget minus actual, or doing cross-sheet data lookups with multiple criteria, formulas are a must for tailoring solutions to your needs.
The new Formula Handbook is here to help formula beginners and pros alike. This template contains:
- A glossary of all Smartsheet functions
- 100+ examples of commonly used and advanced formulas
- A dashboard packed with resources
Have a great idea for the template set? Let us know in the comments or come tell us in person at our customer conference, Engage, this fall!
Licensed users on Business and Enterprise plans can download the template set today! You can also find it and other useful templates by accessing the Solution Center via the marketplace, or by hitting the + button in the left rail in Smartsheet.
You can also stay informed by subscribing to receive product release updates for curated news of recently released product capabilities and enhancements for the platform of your choosing, delivered to your inbox. As new releases occur, you will receive a daily email with news of what's released. (Emails will only send daily, Monday-Friday, as new releases are available).
Allegra
Allegra Silcox
Re: Auto Fill with Dates if certain category is selected
Try this:
=IF([Column Name]@row = "Communication", [Due Date]@row - 7)
Paul Newcome
Re: What formula would I use to read a date from another sheet?
I hope you're well and safe!
Have you created the cross-sheet references?
Try something like this.
=IFERROR(INDEX({Location Tracker - SS Range 1}, MATCH([Studio Code]@row, {Location Tracker - SS Range 2},0)), "")
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: Formula to calculate the last update of a specific column
Hi, I found a way to make that calculation.
When you create a sheet with a Status dropdown menu, you should create a second "tracking" sheet.
In the "tracking" sheet use the first column to "mirror" the column where you have your dropdown menu. Go to each cell of the "mirror" column on the "tracking" sheet and link it to the cell of the corresponding row of the original sheet.
Then, continue working on the "tracking" sheet and set the second column as a Modified(Date) one. It immediately will display the date you created the link to the original sheet on the first column.
Every time you change the dropdown menu cell in the original sheet, the value of the corresponding cell in the "tracking" sheet will also change, and that change will in turn modify the date in the corresponding cell in column 2.
Now, go back to your original sheet. Create a Date column. Go to each cell in that column and link it to the corresponding cell in the Modified(Date) column on your "tracking" sheet. Your original sheet will display the date of the last change in your dropdown menu column for each cell.
Continue on the original sheet and create a Text/number column and write a formula to calculate the difference between today and the date of the last change to the dropdown menu cell.
Example: =TODAY() - ChangeDate2, where ChangeDate is the name of the column that "mirrors" the Modify(Date) column in your "tracking" sheet
bruno.benavides
Re: copy and paste charts
I hope you're well and safe!
Unfortunately, no, but it's still an excellent idea!
To add your vote! Please submit this as a Product Feedback or Idea (If it hasn't been added already) when you have a moment.
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: Dynamic Data Validation with Columns from another Grid
I haven't seen that be an issue before, but that doesn't mean it can't happen. I would suggest reaching out to Support to see what they say.
Paul Newcome
Re: How to select value from last column in a range
Give this a try...
=VLOOKUP([Original Status]@row, [Original Status]@row:[Status 8-30-18]@row, COUNT(Original Status@row:[Status 8-30-18]@row))
The bold portion will be the name of the very last column. I just used the last column shown in your screenshot.
What this does is looks for the value of what is in your Original Status Column and hits against a table. That table is the [Original Status]@row:[Status 8-30-18]@row portion which says to look across the column range in whatever row the formula is in. If it finds the value (or course it will since the value is based off of whatever is in the first column of your table), then it will return the value that is in x number of columns to the right. To determine that x number, you use a COUNT function which counts only non-blank cells. As long as each week up to current has a value in it and each week after is blank, the COUNT function will determine how many columns to the right in that row are not blank. That number is used to determine how far over to the right your VLOOKUP will go for it's return value.
Basically you are saying "Since the value to look for matches, go over to the right however many cells are populated and return that value."
Make your Current Status column a RYG type, and it should work just fine for you.
Paul Newcome
Re: Dependencies and fixed dates
It's really sad that the devs do not implement this little thing which can bring so much satisfaction to the Smartsheet community. I've started using the app less and less, it basically became a glorified online Excel, because I virtually cannot run any project in Smartsheet (due to its multiple shortcomings).
Mihai Nedelcu
Re: How to Auto Delete Duplicate Entries based on a User ID and Date?
The way I do this in my sheet is to have a helper flag column, just like you have! Then I use a formula to count how many rows that match my conditions appear in the current sheet. If there are more than 1 that match, I flag those rows. This way, each duplicate is flagged and I can manually delete one of the two.
For example:
=IF(COUNTIFS([Content for the Week of]:[Content for the Week of], [Content for the Week of]@row, [E Number]:[E Number], [E Number]@row) > 1, 1, 0)
You can then also set up Conditional Formatting so those rows are highlighted in a certain colour to make them stand out.
Let me know if that helps!
Cheers,
Genevieve
Genevieve P.
Re: Smartsheet API in Python: Inaccessible 429 Response
In case anyone finds this thread, I posted this to stackoverflow and received this answer:
Apparently in the OperationErrorResult class, there are a few errors which undergo an auto-retry:
4003: {
'name': 'RateLimitExceededError',
'recommendation': ('Retry using exponential backoff. Hint: '
'Reduce the rate at which you are sending '
'requests.'),
'should_retry': True},
So the error is handled and the request is automatically retried.
If anyone wishes for further details, please see the linked post to stackoverflow
I_B
