Best Of

INDEX and MATCH across two sheets: a detailed explanation

Hello everyone, would love some feedback on this tutorial.

I was answering a user's question regarding INDEX and MATCH (thread post here), and thought it would be beneficial to share my example to the greater Smartsheet community of how to easily utilize INDEX and MATCH functions to reference cells from another sheet by using a shared "identifier" value.

I couldn't find any sources of a true step-by-step tutorial of how to create sheet references to a second sheet range, then show how to utilize INDEX/MATCH to get the info from those ranges.

This tutorial includes a step-by-step example of how to create one sheet as a "data source" sheet, and a second sheet that's used for a more "public" sort of view. The idea here is for the data to have a "home" on the source sheet and a select set of columns available on the other sheet (useful for sharing when you don't want to share all the data from a sheet).

----

Documentation so you can get savvy:

Here's the documentation from Smartsheet for both Index and Match.

Below, I'll try and explain a layman's terms way to do INDEX MATCH - I'd recommend using INDEX/MATCH functions rather than VLOOKUP because INDEX/MATCH functions are more helpful if your data moves around (i.e., if one row gets moved, VLOOKUP can "break").

For your use case, you'll be utilizing what Smartsheet refers to as "Cross-sheet references". You can read about Cross Sheet Formulas (and there's a video) here: https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas

----

Due to limits on characters for posting, I'll add my solution as several responses to this discussion post.

·

Re: Why are unlicensed users asked to upgrade to Premium?

I agree, we are a small medical center with limited funds for licenses, many of our users do not have license, and do not need one, but the "upgrade for a licenses" pop-up makes them think they cannot use or even view sheets until they upgrade. This annoyance has held up many projects, and wasted so much time. We need to be able to turn this function off.

Is it possible to remove the source sheet link from automated email notifications?

Several times a day I get requests to share a source sheet to a user who received an automated alert. Since the body text is small and the source sheet link if big and in a different color, i can understand why they are clicking on it. I have sent out education but it still happens.

Is it possible to remove the source sheet link from automated email notifications?

·

Freezing Top Row in Smartsheet

Any idea if/when Smartsheet will roll out the ability to freeze the top row in sheets?


Thanks!

Re: Row numbers

If all you want is for the ROWID to match the row number I like this formula, which works as long as you point it at a column that has unique values (e.g. an autonumber column). Based on your screenshot I'm using the 'Task' column at the column with unique values:


=MATCH(Task@row, Task:Task, 0)


Now that Smartsheet has Column Formulas I like this one best because it can be used as a column formula, whereas anything that references a specific row can't be. It will also auto-update as you move rows up and down.

Re: I miss the breakout rooms

@cantpickname and @Stacie Karkhoff Thanks so much for sharing your feedback about the breakout rooms. We definitely tried to address the needs for product deep dives and customer walk throughs via some of the webinars and roundtables listed on the Build Your Own Agenda page. Hopefully a couple of these sessions have the content you are looking for.

I will pass this feedback along to our events team so that we can ensure that we are offering this content for upcoming events.

SUCCESSORS Testing

I have seen in the past quite a few people asking how we can leverage predecessors for alerts so that when (for example) the preceding task is complete, a notification goes out automatically to the owner of the next task letting them know it is ready to start. The best approach to this was unfortunately parsing out the predecessors across multiple columns (if you had multiple predecessors).


I have been playing with the new SUCCESSORS function, and think I have almost found a way (with certain limitations). The limitations with this would be if you specified "SS", "SF", "FS", or "FF". I think I have some ideas on how to accommodate that but want to do some more testing before putting it out here for everyone.


In the mean time... Please feel free to take a look at the below to get started . I also welcome feedback and ideas on how to adjust for the other variables such as lag/lead time, "SS"/"SF"/etc., and any other "catch" people can think of.

First we insert an Auto-number column with no special formatting (called "Auto").

Next we insert a text/number column (called "Row") and use the following column formula:

=MATCH(Auto@row, Auto:Auto, 0)


Then we insert a multi-select dropdown column (called "Next Tasks") and used this column formula:

=JOIN(SUCCESSORS([Task Name]@row), CHAR(10))


From here we you can start to deviate and adjust as needed, but the below formula (put into another text/number column) will grab the % Complete for the previous tasks, add them up, then compare it to how many previous tasks there are. If those two numbers match, then the task on this row is ready to start.


=IF(AND(Predecessors@row <> "", [% Complete]@row = 0, SUMIFS([% Complete]:[% Complete], [Next Tasks]:[Next Tasks], HAS(@cell, Row@row + "")) = COUNTIFS([Next Tasks]:[Next Tasks], HAS(@cell, Row@row + ""))), 1)


.


In the below screenshot, you will see that Row 5 is ready to start because its predecessor (row 4) is at 100% complete, and row 5 has not yet been started. Row 9 is not yet ready because it needs both rows 7 & 8 to be completed first and 8 is only at 50%. You will also see that Row 8 is not marked as ready to start because even though its predecessor is completed, the task on Row 8 has already ben started (% Complete greater than zero).


Engage 2021: Together we build Attendee Badge

Hey Smartsheet Community,

If you haven't already seen this badge applied to your profile, please request through the link provided below and our Community Team will be sure to get it added once we've verified your attendance at the Engage 2021: Together we build virtual event:

View Badge: Engage 2021 Together we build

Point Value: 5pts

Please respond in the comments if you have any questions!

Best,

Smartsheet Community Team

ENGAGE 2021: Top 5 Things to Know Before the Show

ENGAGE is tomorrow! I’m so excited for you all to join us virtually for this event. Here are a few helpful tips for you to review before the show: 

  1. Register for the Oct 19th ENGAGE event and access it here (Pro tip: if you registered for the ENGAGE event held June 8th, you're automatically registered for the Oct 19th event and can use the same confirmation code to log in!).  
  2. Tune in at 8:30am PDT for the pre-show to meet our new class of Smartsheet Overachievers
  3. Learn best practices from other Smartsheet users, including folks from Booz Allen Hamilton, Hydro Flask, Dell, and OXO.
  4. Can’t tune in on October 19th? Register here to gain access to the recording. 
  5. Join the conversation on social media by using the hashtag #SmartsheetENGAGE (share your favorite parts of the show!). 

We look forward to having you join us for our biggest event of the year! P.S. Thank you for all your wonderful questions in my last post, Bean and I were happy to help! 

·

Join our Smartsheet ENGAGE 2021 Viewing Party!

Our biggest virtual event of the year is here- ENGAGE! We can't wait for you to tune in and hear from some of our best product experts- your fellow customers!

Interested in joining other can-doers during the show? Jump into our ENGAGE viewing party! This is your chance to connect, chat, and have fun with other Smartsheet enthusiasts! We will start at 8:30AM PT, 30 minutes prior to the event. This will give us a chance to settle in and give out some prizes!

Register today! We look forward to seeing you all there!