Recommended Trainings to learn formulas? Beginner to Advanced

Options
Maricarmen Vargas
Maricarmen Vargas ✭✭✭✭
in Smartsheet Basics

Hello!

I went to Engage19, participated in all formula learning sessions and have even seen the recordings of Engage18.

With this I've just been exposed to possibilities and have applied them by copy pasting the formulas, but I don't really understand the logic of it to actually be able to build my own or think of possibilities by myself, and even be able to help out in forums.

At most, I can slightly edit a formula that is already built or rip apart two formulas end try to make them fit together.


Going to purchase access to Excellence Center but worried I will just see a repetition of Engage tutorials.

Any insight on this?


Also thinking finding Excel formula courses, since I've read its the same thing, except formulas are written a bit differently in Smartsheet, but at least I can learn the logic.

Any recommendations? Thanks

Tags:
· Share on FacebookShare on Twitter

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    @Maricarmen The Center of Excellence is definitely a great starting point. Mostly though practice and experimentation are how I learned the most. A good tip to always keep in mind...


    Start small.


    Break everything down into as small of pieces as you possibly can. If your overall goal is to pull data from a source sheet to be displayed on your target sheet based on values in a table, start by looking at your options for pulling data. HERE is a link to a listing of each function, and at the bottom of this post will be a screenshot of a very helpful template you can download.


    So after perusing your different options, you settle on the INDEX function.


    =INDEX(range_to_pull_from, row_number, optional_column_number)


    The first bit is easy enough. Where are you pulling your data from? Next you need to establish a row number. But let's face it, the whole point of a formula is to minimize manual entry. You want to automatically generate a number for this section, so you look at your resources and figure out which functions will generate a number.


    Depending on exactly how you want things to work, there could be any number of options. The most popular combo with INDEX is MATCH, so we will go with that.


    MATCH generates a number based on where within a range specific data was found. If you are looking down a single column, this number will essentially be the row number. So let's momentarily forget about the big picture and the INDEX function and focus on the MATCH.


    =MATCH(data_to_search_for, range_to_search, optional_type_of_match)


    First bit... What are we looking for? Let's just say we are looking for the data in [Task Name]@row. Second bit, also easy. Where is the range that we want to look for the task name in? Third bit is optional, but I always recommend 0 (zero). It looks for an EXACT match and (in my opinion and experience) has provided the most accurate results). SO lets drop this mess into our MATCH.


    =MATCH([Task Name]@row, {Other Sheet Task Name Column}, 0)


    Alright. So that is done. Now we can go back to our INDEX function. So far we have


    =INDEX({Other Sheet Pull Column}, row_number, optional_column_number)


    but we just figured out how we are going to establish the row number; by using the MATCH statement. So we knock the "=" off of the beginning and just drop the whole thing into the second portion of our INDEX function.


    =INDEX({Other Sheet Pull Column}, MATCH([Task Name]@row, {Other Sheet Task Name Column}, 0), optional_column_number)


    We only selected one column to pull from, so the third bit being optional means we can delete it.


    =INDEX({Other Sheet Pull Column}, MATCH([Task Name]@row, {Other Sheet Task Name Column}, 0))


    The same thought process can be used for nesting IF statements or anywhere else statements are used within other statements. Just break it down, work out each piece individually, then put it all back together.

    One way to help do this in an actual sheet is to set up a bunch of extra columns (they'll be deleted after the formula is built). Put each portion in it's own column across the same row and use cell references instead. So for the formula above, we would have used two columns. One for the INDEX and one for the MATCH. When writing out the INDEX, we would have referenced [Match Column]@row like so.


    =INDEX({Other Sheet Pull Column}, [Match Column]@row, optional_column_number)


    Then you would put the Match function in the corresponding column. This way you can test and trouble shoot each portion individually. It may not seem necessary for an INDEX/MATCH, but when you get into more complex formulas with lots of moving parts, breaking it down will make life a whole lot easier for you.


    Once you have every part working on it's own, you can go to the cell [Match Column]@row, open it up, then copy/paste the data into the portion of the INDEX formula where it references that cell.


    Hopefully that all makes sense. Long story short... Break things down and work small. THEN piece everything together into the larger/more complex formulas.


    Here are some resources.


    FUNCTIONS LIST

    FORMULA ERROR MESSAGES/MEANINGS/TROUBLESHOOTING TIPS


    Screenshot of a template definitely worth saving:

    Comm1.PNG


    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    @Maricarmen The Center of Excellence is definitely a great starting point. Mostly though practice and experimentation are how I learned the most. A good tip to always keep in mind...


    Start small.


    Break everything down into as small of pieces as you possibly can. If your overall goal is to pull data from a source sheet to be displayed on your target sheet based on values in a table, start by looking at your options for pulling data. HERE is a link to a listing of each function, and at the bottom of this post will be a screenshot of a very helpful template you can download.


    So after perusing your different options, you settle on the INDEX function.


    =INDEX(range_to_pull_from, row_number, optional_column_number)


    The first bit is easy enough. Where are you pulling your data from? Next you need to establish a row number. But let's face it, the whole point of a formula is to minimize manual entry. You want to automatically generate a number for this section, so you look at your resources and figure out which functions will generate a number.


    Depending on exactly how you want things to work, there could be any number of options. The most popular combo with INDEX is MATCH, so we will go with that.


    MATCH generates a number based on where within a range specific data was found. If you are looking down a single column, this number will essentially be the row number. So let's momentarily forget about the big picture and the INDEX function and focus on the MATCH.


    =MATCH(data_to_search_for, range_to_search, optional_type_of_match)


    First bit... What are we looking for? Let's just say we are looking for the data in [Task Name]@row. Second bit, also easy. Where is the range that we want to look for the task name in? Third bit is optional, but I always recommend 0 (zero). It looks for an EXACT match and (in my opinion and experience) has provided the most accurate results). SO lets drop this mess into our MATCH.


    =MATCH([Task Name]@row, {Other Sheet Task Name Column}, 0)


    Alright. So that is done. Now we can go back to our INDEX function. So far we have


    =INDEX({Other Sheet Pull Column}, row_number, optional_column_number)


    but we just figured out how we are going to establish the row number; by using the MATCH statement. So we knock the "=" off of the beginning and just drop the whole thing into the second portion of our INDEX function.


    =INDEX({Other Sheet Pull Column}, MATCH([Task Name]@row, {Other Sheet Task Name Column}, 0), optional_column_number)


    We only selected one column to pull from, so the third bit being optional means we can delete it.


    =INDEX({Other Sheet Pull Column}, MATCH([Task Name]@row, {Other Sheet Task Name Column}, 0))


    The same thought process can be used for nesting IF statements or anywhere else statements are used within other statements. Just break it down, work out each piece individually, then put it all back together.

    One way to help do this in an actual sheet is to set up a bunch of extra columns (they'll be deleted after the formula is built). Put each portion in it's own column across the same row and use cell references instead. So for the formula above, we would have used two columns. One for the INDEX and one for the MATCH. When writing out the INDEX, we would have referenced [Match Column]@row like so.


    =INDEX({Other Sheet Pull Column}, [Match Column]@row, optional_column_number)


    Then you would put the Match function in the corresponding column. This way you can test and trouble shoot each portion individually. It may not seem necessary for an INDEX/MATCH, but when you get into more complex formulas with lots of moving parts, breaking it down will make life a whole lot easier for you.


    Once you have every part working on it's own, you can go to the cell [Match Column]@row, open it up, then copy/paste the data into the portion of the INDEX formula where it references that cell.


    Hopefully that all makes sense. Long story short... Break things down and work small. THEN piece everything together into the larger/more complex formulas.


    Here are some resources.


    FUNCTIONS LIST

    FORMULA ERROR MESSAGES/MEANINGS/TROUBLESHOOTING TIPS


    Screenshot of a template definitely worth saving:

    Comm1.PNG


    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter

Categories

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video