Use a Row Number with Index Collect to return multiple rows

Options
This discussion was created from comments split from: Index Collect.

Answers

  • Gita Mooney
    Gita Mooney ✭✭✭
    Options

    How does the =[Type of Fruit]:[Type of Fruit] work if i have to use an external sheet (another sheet0? I tried {Fruit Range 1}@row, but that did not work. So i want to bring back one field (Site #) based on matches for three columns.

    =INDEX(COLLECT({PM Services Range 1}, {PM Services Range 2}, "PM Generator", {PM Services Range 3}, @cell  <>"", {PM Services Range 4}, "Approved"), 1)

    It brings back the first row correctly. When I try to make it a column formula, it populates the same value from the first row all the way down.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Gita Mooney

    Your formula is correct!

    The 1 at the end of your formula indicates that you want to bring back the first match. If you have more than 1 row that meets your criteria, you would need to swap out that 1 to be a 2, then 3, then 4, and so on.

    =INDEX(COLLECT({PM Services Range 1}, {PM Services Range 2}, "PM Generator", {PM Services Range 3}, @cell  <>"", {PM Services Range 4}, "Approved"), 1)

    Second row:

    =INDEX(COLLECT({PM Services Range 1}, {PM Services Range 2}, "PM Generator", {PM Services Range 3}, @cell  <>"", {PM Services Range 4}, "Approved"), 2)

    Third row:

    =INDEX(COLLECT({PM Services Range 1}, {PM Services Range 2}, "PM Generator", {PM Services Range 3}, @cell  <>"", {PM Services Range 4}, "Approved"), 3)

    To make it easier, you could have a column that lists out the numbers (e.g 1 - 100) and then reference that cell instead:

    =INDEX(COLLECT({PM Services Range 1}, {PM Services Range 2}, "PM Generator", {PM Services Range 3}, @cell  <>"", {PM Services Range 4}, "Approved"), [Number Column]@row)

    Then you can make it a column formula.

    Cheers,
    Geneveive

  • Gita Mooney
    Gita Mooney ✭✭✭
    Options

    Can i use the Smartsheet row number? Also, if I do it today and it brings back all the columns that exist today, and tomorrow they add a new row, will it bring back only the new row going forward?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Gita Mooney

    There isn't a way to reference the direct row number in a formula; you could set up helper columns to generate the row number, but honestly I think it would be easier to simply add "1" into a cell, then "2" into the next cell:

    and then drag-fill down the column to populate the rest of the numbers:

    As long as the number exists in this current sheet (the one with the formula), then new rows that are added in the source sheet will appear. However if you don't list enough numbers, then the formula will stop to whatever number you have listed - does that make sense?

    Another option would be to use a Report instead, since that will automatically bring in new rows as they're added to the source sheet.

    Cheers,
    Genevieve

  • Gita Mooney
    Gita Mooney ✭✭✭
    Options

    Thank you so much. can you please help me by explaining how do i do a report to bring in new rows?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Gita Mooney
    Reports are essentially windows into underlying sheets. Depending on your Filter criteria, as soon as sheets are updated, the Reports are updated.

    Here's a related Help Article: https://help.smartsheet.com/articles/2482078-build-a-row-report-with-report-builder

    Here's a free webinar to help you get started:

  • Gita Mooney
    Gita Mooney ✭✭✭
    Options

    To clarify, the source sheet has multiple rows and multiple columns. My destination sheet i only need to bring in 3 columns based on the conditions i had listed, first for existing rows and then as new rows are added. is that possible.

    Thank you for the links. I will definitely use them to figure out the report.

    Thanks again so much

  • Gita Mooney
    Gita Mooney ✭✭✭
    Options

    To clarify, the source sheet has multiple rows and multiple columns. My destination sheet i only need to bring in 3 columns based on the conditions i had listed, first for existing rows and then as new rows are added. is that possible.

    One last question. When i create a row column, is there a way to set it equal to the Smartsheet row id? When people delete rows, the sequence gets broken. So if I delete 2 from rows 1-5, now I have row 1,3,4,5. Any way to do that?

    Thank you for the links. I will definitely use them to figure out the report.

  • Gita Mooney
    Gita Mooney ✭✭✭
    Options

    i know I am bugging you , but i found one more issue. In the reference column -  {PM Services Range 2}, "PM Generator",  - I have to look for multiple values. The moment I add the second value, it breaks it. Are these AND conditions? How can I make just the Range 2 one be an OR with multiple values?

  • Gita Mooney
    Gita Mooney ✭✭✭
    Options

    I created the report but the article or the video does not show how to use that report to update my destination sheet

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Gita Mooney

    Thank you for clarifying your set up! This helps a lot.

    A Report is a good way to filter current sheets or bring sheets into one place, but it cannot take rows from one sheet and merge it on a different sheet. You are correct that a formula would be a better way to surface this data.

    Do you have something in your current sheet (where you're building the formula) that is unique so that we can map that to the first sheet? It can be a combination of values (e.g. if look at these 3 cells in the row, it will map to a unique row in the source sheet).

    The 1, 2, 3, at the end of the formula brings back the duplicates, so if you have more than 1 match, but as you noted it's not the best for scale. The better thing would be to match on something unique.

    You could also use JOIN(COLLECT instead of Index, which will join together all values matching:

    =JOIN(COLLECT({PM Services Range 1}, {PM Services Range 2}, "PM Generator", {PM Services Range 3}, @cell  <>"", {PM Services Range 4}, "Approved"), " / ")

    For your multiple values, do you mean that it's a multi-select cell and you need both selections to be in the same cell? Or are you looking for multiple possibilities down a column, in different rows?

    At this point it would be helpful to see screen captures of both sheets, clarifying exactly what it is you're looking to bring back into your destination sheet. It may be best to set up a Copy Row workflow when a row is added to your source sheet, copying over all the data.

    Cheers,
    Genevieve

  • Gita Mooney
    Gita Mooney ✭✭✭
    Options

    Genevieve, Thank you for all your help. It worked. the Index/Collect only allows you to search for one value per range, the moment you add the range again and put in the second value, it freaks out. So i created a new field in the source sheet, made all the values i did not need to be BLANK and told the Index/Collect to pull all the values that were NOT BLANK. So all is well. Thanks again for all your help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!