Best Of
Query Strings - Development
So I've developed several processes that fully rely on query strings for a variety of reasons, and many of my users find it difficult to reverse engineer my sheets to develop similar solutions, even with the official smartsheet help guide. I've made a fairly basic tutorial so I don't have to keep explaining it, and thought it might be useful to some in the community.
My reference:
Official Smartsheet Reference:
L_123
How to Flag Spam Posts in the Community
Hi all,
Over the last couple of days we've noticed an increase in spam in our Community forum.
Spamming is against our Community Guidelines. If you happen to come across a post containing spam, abusive language, or anything else that crosses our guidelines, please Flag this post for our Moderation team to review:
Thank you for your help in keeping the Smartsheet Community full of good-quality content!
Cheers,
Genevieve
Ranking without Duplicating Ranks
I used the RANKEQ function for the first time recently and realized that any identical values will have the same rank. It will skip however many duplicates you had in the ranking numbers, so you may have a ranking scheme that looks like 1, 2, 3, 3, 3, 6, 7, 7, 9, 9.
This may be useful in some situations but it is actually problematic in other situations. Let's say I am searching for the top 10 values in my set. I will not find a value for 4, 5, 8, or 10. This will of course be frustrating if I want to simply plot the top 10 values from my data set.
I have created the following formula to ignore duplicates and simply increment the rank of any duplicates by 1. This means any duplicates will have an arbitrary ranking to each other.
In this formula, "A" is the column being ranked.
RANK =RANKEQ([email protected], A:A) + COUNTIF(A$1:[email protected], RANKEQ(@cell, A:A) = RANKEQ([email protected], A:A)) - 1
Hopefully this helps someone!
#Invalid Ref error in a report, but not the source
I'm seeing an invalid reference error in a report on a dashboard, but the source data is good and the vlookup is pulling in the data I want to see.
Any thoughts on the cause/solution? I've narrowed down, deleted and re-created the cross sheet reference several times and the error persists.
Screenshots of the error and data source are below.
Jason_W
Re: How to include specific parent and child rows from multiple sheets in a report
Hello @Christiana Gkini
This is currently not supported in Reports. When you can, please submit an enhancement request to have this be considered for future development in Smartsheet!
In the meantime, an alternate solution is to change the background color for parents/children OR use conditional formatting to differentiate the parents/children in the report. See image below as sample.
Hope this helps!
Thank you~
Free Smartsheet Advance Webinar: Intro to WorkApps
Join us on December 8th for a free webinar "Intro to WorkApps" in this webinar we will be learning about how to create and share a customized experience of data, content, and resources, based on roles with WorkApps.
We will be hosting 3 sessions across multiple timezones, if you are not able to join us live, feel free to still register and we will share the recording with all registrants following the webinar!
Enhanced Community Search is Now Live!
Hey Smartsheet Community,
The Community Team is excited to announce a new and exciting way to search, giving you the confidence to find exactly what you’re looking for when you need it most.
We’ve enhanced our search tool so you can now search for relevant and up-to-date Community posts.
In addition to these changes, we’ve narrowed down the filters that matter the most to you and now offer capabilities such as:
- List view of groups
- Filter by top tags
- Sort by post relevance, and more
Have questions? Check out our quick FAQ below:
Q: What has changed from the previous search to the new?
A: All filters are visible within the left sidebar of every single search page, along with four primary sections to utilize for narrowing down your search.
1. Last Updated: You’ll be able to search from the Last day to More than a year in time
2. Tags: View a list of the most popular Community tags, with an additional search option
3. Groups: Search through a list of all live groups and the number of associated posts
4. Category: See a list of all categories and the number of associated posts for each category
Q: Are there additional ways to filter posts through?
A: Yes! There is a new Sort by: function that lives directly below the search bar where Community Members can sort by Relevance, filtering through all keywords of what other members have searched for. Newest provides members the option to filter through most recent posts and oldest.
Search is a vital part of our Community and we couldn’t have done it without you. If you have any feedback, please submit your thoughts through this Smartsheet Community Feedback Form so we can continue to improve.
Thank you,
Smartsheet Community Team
Re: Index Match Formula Help
You could use a VLOOKUP, you're correct!
However I personally prefer INDEX(MATCH because it evaluates the two columns separately.
In a VLOOKUP you put in one range that spans across all your columns, with the first column in the range being the one with the Match and the last column (or one of the columns) being the value to return. Then you identify what column in that range you want returned.
So, in the instance where these columns are next to each other (therefore your VLOOKUP range is only 2 columns), then VLOOKUP and INDEX(MATCH would be equal in compatibility.
However, think about if you have a sheet where your Return Value and your Match Value are in columns that are quite far apart... for example 10 columns apart. Your VLOOKUP range would have to select all those columns in one go and could potentially break if someone edited the source sheet and moved columns around. As your sheet gets more complex, having a large range selected where a formula has to evaluate all those additional columns could slow it down.
This is where INDEX(MATCH is more flexible: you select the column to Return in the INDEX function, then the MATCH function uses the Column with the Matching Value to identify the row to bring back. Since you're referencing each column individually, you can move them around in the source sheet and the Index(Match will follow each column as it moves. It also eliminates any of those in-between columns from needing to be read through, which will keep your referenced cells to a lower number and you'll be less likely to hit reference cell limits.
Does that help clarify? I'd be happy to post screen captures to explain what I mean about the ranges, if that would be useful.
Cheers!
Genevieve
