Sort data conditionally from one sheet to another
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
-
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
-
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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!