Best Of
Using a nested IF to determine a range
Here's a little tid-bit I just figured out today... You can use a nested IF statement to output ranges/cross sheet references that are to be evaluated.
The particular use case that led to this was an INDEX/MATCH where we wanted to INDEX on a single date column, but MATCH on one of 4 different columns depending on an Asset Type. We ended up being able to use a nested IF for the "range to search in" portion of the MATCH.
=INDEX({Asset Checkout Range 3}, MATCH([email protected], IF([Asset Type]@row = "Camera", {Asset Checkout Range 1}, IF([Asset Type]@row = "Support", {Asset Checkout Range 2}, IF([Asset Type]@row = "Lighting", {Asset Checkout Range 5}, {Asset Checkout Range 6}))), 0))
I personally like this a little better than writing out four different INDEX/MATCH statements and using IFERRORs to account for the #NO MATCH issue which would have looked like this...
=IFERROR(IFERROR(IFERROR(INDEX({Asset Checkout Range 3}, MATCH([email protected], {Asset Checkout Range 1}, 0)), INDEX({Asset Checkout Range 3}, MATCH([email protected], {Asset Checkout Range 5}, 0))), INDEX({Asset Checkout Range 3}, MATCH([email protected], {Asset Checkout Range 1}, 0))), INDEX({Asset Checkout Range 3}, MATCH([email protected], {Asset Checkout Range 6}, 0)))
We saved 93 characters/keystrokes and only had to reference the INDEX range and the "data to match on" for the MATCH function once each instead of 4 times, and I can't say for sure, but I also feel like this might be a little more efficient on the back-end as well.
I feel like there are a lot more practical applications too.
Here is a link to the thread that prompted this...
Re: Master and metrix sheets
Ok. It looks like you are primarily going to want to use COUNTIFS functions.
Here is some documentation on cross-sheet references that may come in handy:
Most of your formulas are probably going to end up looking pretty similar to:
=COUNTIFS({Main Data Sheet Site Column}, @cell = "Site Name", {Main Data Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021), {Main Data Sheet Checkbox Column}, @cell = 1)
This will count how many rows match the specified site, are in the month of January and year of 2021, and have that specific checkbox checked.
Register now for ENGAGE 2021 Special Edition!
Join us online on Tuesday, June 8th from 9-10:30 a.m. PDT for a special virtual edition of ENGAGE!
You’re invited to ENGAGE ALL IN, a special virtual edition of our customer conference, where we’ll help business leaders like you unleash the potential of your workforce so you can deliver on the demands of today’s world.
This year, we’re holding two virtual events: register for the June 8th event here, and save the date for our Fall ENGAGE event on October 19th. More details coming soon.
Questions about our upcoming event? Post them in the comments below! We have so much to share with you and can't wait to connect with you all in June!
Re: The new Smartsheet experience
Thank you to everyone who has taken the time to provide us with detailed feedback.
Smartsheet takes great care and consideration when introducing new design elements into the app, and we test all changes thoroughly to ensure our choices are the best solution for our customers as a whole. The new Smartsheet layout and interface should help our customers get started and use Smartsheet more quickly and easily, and lays the foundation for a series of powerful enhancements to the product that will roll out over the next year.
A number of you have noted the changes we have made to both the grid and the column headers.
This has been a popular piece of feedback since we’ve launched the new Smartsheet experience.
Here is what we’ve heard so far:
- The navigation bar needs to be adjustable or collapsable, it takes up too much space
- The toolbar above sheets takes up too much space
- Column headers on sheets and reports are too large and you can’t adjust the height
These are all valid pieces of feedback and we are actively looking at options. If your feedback is not accounted for on the above list, please submit an enhancement request and we’ll be sure to address it.
We will be releasing an update during the week of April 19th that will reduce the amount of empty space above column names. In the meantime, there are two features you can leverage in order to maximize your working space in sheets:
1. Smartsheet has a ‘full screen’ view built into sheets. Look for the icon in the bottom left corner of a sheet - this is how you enter full-screen view. In this view, the navbar and toolbar disappear, maximizing your work area in a sheet.
2. There is also a small chevron icon right under the share button. Clicking this icon will collapse the toolbar, giving you more usable space in your sheet.
We are working on updating our help article to consolidate all of these great questions so that we can respond to them in one place. We will continue to update this help article as we see questions come in.
We want to thank you again for taking the time to provide this valuable feedback. As we continue to review each comment and share it with our product team, we will send you a private message within Community to help ensure that your voice is heard. Please look out for a response in the coming week.
Note: You can access your private messages by clicking the mail icon in the upper right-hand corner of your screen.
Thank you,
Brendan
Brendan Reed
Work Breakdown Structure (WBS) - Column Formula Configuration
Hello Everyone,
Recently myself and two fellow Smartsheet Sales Engineers, @Lea Gikas and @Dave Stanley got together to create a WBS solution powered by Column Formulas.
We wanted to provide the resulting configuration to the community in case some may find it helpful in executing project management solutions, this configuration supports up to ~2,300 tasks & metrics are built out through WBS Level 6.
The solution uses 7 hidden and locked column formulas, and optional Summary Sheet formulas that provide WBS related metrics.
Here is a distribution link to create a copy in your account; start creating tasks and sub-tasks and the configuration will activate - https://app.smartsheet.com/b/launch?lx=AoXtVB0iMz0XS5D-MVU5jl2F3tjZfBYMXSEruozjq1E
We hope this is helpful and let us know if you have any questions.
Regards, Tom Zylstra, Lea Gikas, Dave Stanley
Tom Zylstra
Re: Best way to speed up Smartsheets
Hello! We have a sheet we use to track member payments. At around 5k rows it started slowing down and the form to submit new payments stopped working. I reported it because this sheet and the related resources are used by about 30 users every day and suddenly everything came to a halt.
Smartsheet reviewed my sheet and suggested the following: simplify conditional formatting (I admit it was a little out of control with about 30 rules) and replace VLOOKUP formulas with INDEX/MATCH formulas (we're doing numerous lookups from another sheet to pull assigned resources, client name, etc.).
Neither of these things solved my problem, so I got creative and created some additional sheets to help mitigate the row overload on the primary sheet. The two most active clients (S24, FF1) each got moved to their own sheets. Automation was put in the place to move appropriate rows when either of those two client numbers were entered by one of our clerks on the primary sheet. I hadn't used that the move automation previously, but works like a charm and led to the next change.
Also, I created an separate intake sheet to accept new payments via a form. As the primary sheet grew in row count, the original form became incredibly slow - maybe taking 30-45 seconds to submit each entry. Now payments are added to this intake sheet and moved once an hour using automation. Each payment submitted now probably takes 2 seconds.
All of this moving records got me thinking, so I added a payment rejection routine. When a user in our Eligibility department finds a check that has been routed incorrectly, they enter reject as the client number and automation moves that row to the rejection sheet, which will trigger notification to our Accounting department that they have a rejected check to review.
Below is my workflow diagram for record movement if a visual helps.
Re: Forms Announcement: New enhancement for conditional logic
Update on February 25: Conditional logic for headers and dividers in forms is now available!
Hi all,
I love the enthusiasm for this new feature to conditionally show headers and dividers on your form! We identified an issue with the enhancement, so we temporarily turned it off so that we can address the issue and re-enable it for you. If you don't change anything on your form your conditional logic rules on headers and dividers will continue to function on the form your users open. However, if you go back to your form builder and re-save the form that logic will be removed and you won't see it as an available option in the form builder. We expect this feature to be available again shortly and I will update this post as soon as it is available again. We will also update the release notes with the status when it's available. Please respond to this post with any further questions.
Re: Status Update: Smartsheet Supported DocuSign Integration
@Andrée Starå thanks for following up! The upcoming integration will be a hit, and the recent release of Document Builder brings us one step closer to creating powerful document workflows in Smartsheet.
With that said, I've checked in with our Product team about this. They have confirmed we are actively working on this feature and expect it to be released around the end of year, however I do not have an exact date for when this may be. I'll update the post's title and be sure to update this post as soon as more dates become available.
Per the topic of deprecation, the new Smartsheet supported integration will be available with ample overlap before we turn off the unsupported DocuSign Labs integration. For previous Labs users, we have not yet turned off the DocuSign labs integration and you still will be notified 60 and 14 days prior.
Additionally, many of our new capabilities get released ahead of time to our Early Adopter Program so members can test, build innovative workflows, and share critical feedback before the capability is available to all. Anyone that is interested in exclusive, early access to some of our newest capabilities before they are released to the public should check out the EAP page and apply to become a member.
Thanks,
Hunter
Hunter Reed
NEW updates to our community!
Last week you may have noticed some updates to our current community! While a number of these updates were visual updates around the look, feel, and navigation of the site, there were also a few functionality updates we wanted to share with you as well:
- My communities and my groups- when logged in you will now see a list of all the communities you follow or the groups you have joined in the side rail of your homepage.
- New communities and groups drop down- Under “Recent Discussions” you will see a drop down that allows you to view recent discussions specific to your selected community or go to our groups page to view our currently available groups
- Updated profile pages: profiles better highlight your earned badges and offer fun default images for your profile image
As always thanks for your participation in our community and we look forward to continuing to provide further improvements in the months ahead!
Re: What is the best way to learn SmartSheet?
"Hierarchies being inside of a program means someone has to touch every row to put them in the correct hierarchy"
Yes, but one of the advantages with how my team generally uses SS is that we use templates (of sorts). This means row hierarchy is only touched one time during the initial build and then no one needs to worry with it again (aside from the occasional tweak here and there which is usually verbiage an not hierarchy related).
I have 15 different project plans that I built into one giant monster template. The very first thing my team does is run through a series of checkboxes to determine exactly which project plan(s) they need. Once they do that all they have to do is apply a filter to hide the rows that aren't applicable and then enter the appropriate actual start and actual end dates.
Basically we offer different services, but all services have a 10 week setup time, and if the same milestone is used for multiple services, it is due at the same time. So setting up banking is done at the same time within the overall project regardless of which service(s) we are implementing and will always have the same sub-tasks.
I only had to touch my hierarchies one time for the initial template and now only need to account for the rare tweak.
This makes it so that the hierarchy is already established and the users don't need to mess with anything other than a few checkboxes, a filter, and then two date columns.
The actual setup is much more complex than that, but that's the general idea.
I use the same concept for my YTD tracking. I spent extra time setting up the 1st year making sure it was perfect and everything was linked together so that for the following years all I have to do is save a folder as new then go into a single sheet and update the year (cell links carry the update to the rest of the metrics sheets).
So I don't have to adjust those hierarchies anymore either.
@Catherine Hall Look at you stirring the pot with your very first post. Hahaha. Just kidding. 😜
As you can see there are a lot of different ways to approach things in Smartsheet depending on your needs. The "best" solution for you may be something very different than what works for me or anyone else.
One of the things I like about this Community is that different opinions and additional options for solutions are definitely not frowned upon or considered "stealing someone's thunder".
The Smartsheet Community (in my experience) has been one of open knowledge sharing where even "simple" questions aren't "judged" or "looked down upon" and are answered without a condescending attitude.
Compared to other "communities", the Smartsheet Community has been one of my personal favorites to interact with.
So... Welcome and best of luck (and don't forget the coffee and bubble wrap 🤣)!
Paul Newcome