Getting Data From Another Sheet to Populate in Another Sheet when Conditions are Met

egold31

Hello,

I currently have 2 sheets that I am working with: the first is the "Original" sheet and the other is a "Metrics" sheet, which pulls data from the original (being used for Dashboard charts).

The "Original" sheet has an "ID" column, a "Status" column (either "OPEN" or "CLOSED"), and "# of Days" column. On the metrics sheet, I would like all of the IDs to automatically populate when the Status is "CLOSED" on the Original sheet. There are many IDs at one time on the Original sheet that have a "CLOSED" Status.

How can I get multiple IDs to populate in consecutive rows using conditionals and external sheet referencing. Ideally if the "CLOSED" status on the Original sheet = true, I would like the ID, Status, and # of Days for all the applicable entries to appear on the metrics sheet.

I utilized the following equation: =INDEX({ID}, MATCH("CLOSED", {Status}, 0))

However, this is ONLY returning the 1st ID for which the Status = "CLOSED" on the Original sheet. I would also like the other 6 IDs for which the Status = "CLOSED" to appear on the Metrics sheet. My main question is to how I can have multiple column values (ID, Status, # of Days) appear on the Metrics sheet when one conditional is met on the Original data sheet and for ALL qualifying pieces of data.

Thanks for all your help!

KPH

Could you swap the INDEX MATCH for an INDEX COLLECT. The COLLECT is the range to be returned which allows you to specify the row index as well. Take a look at:

https://help.smartsheet.com/function/collect

egold31

This worked thank you very much! One more question: is there a way to put each data value that is returned in a separate column. I used CHAR(10), but this puts each number on a different line in the same cell.

My formula is: = JOIN(COLLECT({Info}, {Status}, "DONE"), CHAR(10))

I appreciate the help!

KPH

The JOIN will combine everything that meets the criteria in the COLLECT.

If you use INDEX, it will return just one of the things (you can specify the row).

You will need to put an INDEX function in each column with a different row index for each.

=INDEX(COLLECT({Info}, {Status}, "DONE"), 1)

=INDEX(COLLECT({Info}, {Status}, "DONE"), 2)

etc.

egold31

@KPH Thank you for your response!

The only issue I see using INDEX is that fact that the number of items where the Status = "DONE" changes each day. So manually inputting row indexes will not work in this case. However, is there a way to use indexing or even a loop for the row value? For instance, if there are 6 items where Status = "DONE" how could I use indexing to accommodate for the changing number of values that meet the criteria?

KPH

You could use a COUNTIF to count the number of rows that meet the criteria and put that into the INDEX instead of a static number for the row index. That will give you the last row.

However, I don't think that will be of any benefit.

You would have one formula in each column.

This INDEX goes in the first column

=INDEX(COLLECT({Info}, {Status}, "DONE"), 1)

and will return the 1st row that matches the criteria

This INDEX goes in the second column

=INDEX(COLLECT({Info}, {Status}, "DONE"), 2)

and will return the 2nd row that matches the criteria

This INDEX goes in the third column

=INDEX(COLLECT({Info}, {Status}, "DONE"), 3)

and will return the 3rd row that matches the criteria

If there is not a 3rd row, it will return an error. You can add an IFERROR function so that it returns nothing instead of an error.

=IFERROR(INDEX(COLLECT({Info}, {Status}, "DONE"), 3),"")

As long as you have enough columns for the maximum number of DONEs in a day, it should be fine. You can always use a COUNTIF function to check a box or something if the number of DONEs exceeds the number of columns you have and trigger an automation or some conditional formatting.