Use a Row Number with Index Collect to return multiple rows

System

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

Gita Mooney

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.

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

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.

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

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

Genevieve P.

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:

https://help.smartsheet.com/learning-track/onboarding-webinars/smartstart-reporting

Gita Mooney

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

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

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

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.

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

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.