Sort data conditionally from one sheet to another

lmiller
lmiller ✭✭
edited 01/18/22 in Formulas and Functions

Hello,

I am looking to take the rows from one sheet that have "x" in column 15 and output all of them in another sheet. the closest that I've gotten is:

=INDEX({Page 1 Range 1}, MATCH("X", {Page 2 Range 2}, 0), 1)


Which when placed in row 1 column 1 on Page 2, pulls the first matching result in row 1.


I have each column like so:

=INDEX({Page 1 Range 1}, MATCH("X", {Page 2 Range 2}, 0), 1)

=INDEX({Page 1 Range 1}, MATCH("X", {Page 2 Range 2}, 0), 2)

=INDEX({Page 1 Range 1}, MATCH("X", {Page 2 Range 2}, 0), 3)

etc...

in order to pull a full row into another page.


However, this only works for the first match for "X". Trying this in a different row outputs the same data.

I'm sure that there is a better way to do this. In excel, I would have used the FILTER function, but in Smartsheet that isn't an option. Any advice?

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    There are a few ways to do this.

    If this is a one-time thing, like you just want to extract those rows with X in column 15, then I would recommend creating an automation rule, set it to trigger at a certain time, to copy all rows with X in column 15 to another sheet.

    If this needs to be a regular thing, add a helper checkbox column to your sheet, call it "Moved?". Create your automation rule with trigger as "When rows are added or changed", when Column 15 changes to X. Add a condition block, where 'Moved?' column is unchecked. Set the Action to copy the row to the other sheet, and add another action to change the Moved? column to checked.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    There are a few ways to do this.

    If this is a one-time thing, like you just want to extract those rows with X in column 15, then I would recommend creating an automation rule, set it to trigger at a certain time, to copy all rows with X in column 15 to another sheet.

    If this needs to be a regular thing, add a helper checkbox column to your sheet, call it "Moved?". Create your automation rule with trigger as "When rows are added or changed", when Column 15 changes to X. Add a condition block, where 'Moved?' column is unchecked. Set the Action to copy the row to the other sheet, and add another action to change the Moved? column to checked.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • lmiller
    lmiller ✭✭

    That sounds great!


    I attempted to do this by adding the trigger "When rows are changed" "when any field changes" and to run when triggered.

    The condition was "Where Column is equal to x" with no otherwise selected

    And the action was to move the rows to the second sheet.


    This does not work for some reason. Any ideas why?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!