How copy data from one sheet to another with multiple filters

Hi. I'm trying to get the data from a huge source sheet (40.000+ rows) into a destination sheet that should collect just the rows that match 3 conditions.

We are talking about students:

SOURCE SHEET COLUMNS

ID - Name - Surname - Status - Course A - Course B

12 Mark Smith Enrolled Yes No

....

In the destination sheet I would need to see just the rows that match these 3 conditions:

Status is Enrolled

Course A is Yes

Course B is No

I started from this formula but obviously I'm getting just 1 result based on 1 filter:

=INDEX(COLLECT({source_ID}, {source_status}, "Enrolled"), 1)

Thanks.

Find more posts tagged with

collect

Accepted answers

All comments

NickStaffordPM

You could just make a report with your source sheet as the data source. No need to create second sheet I believe if I understood correctly. You would be able to set filters to meet those criteria you outlined, and that way only those Rows would poplate. You would also be able to customize what columns from the source sheet were visible on this report. You would be able to edit data on this report and it would update the orginal sheet.

LeoT

Hi Nick, thanks for the reply.

The source file is so important we don't want to use it. This is why we decided to create a new sheet with the required columns in it. We also need to manually add some information on the destination sheet so unfortunately, a report doesn't help.

I found this way to proceed. I have added a column called "Nr" at the beginning of the destination sheet with sequential numbers (1,2,3 etc) and I have used this formula to the column:

=INDEX(COLLECT({source_ID}, {source_status}, "Enrolled"), Nr@row)

The formula worked well. I just need to understand of there is a way to use the filname+column name instead of the custom reference name. Do you know if it's possible?

jmyzk_cloudsmart_jp

Hi @LeoT

First, as a destination sheet, you need all the student IDs and columns such as Name, Surname, and, if you want to show, Status, Course A, and Course B.

Since Smartsheet currently has a 20,000-row limit, you must divide the source and destination sheets to fit this limit. (Please check the published demo dashboard in the following link.)

https://app.smartsheet.com/b/publish?EQBCT=b3a1a743b1ff44fe9c7415bd7a119d36

Then, the formulas at the destination sheets to retrieve data from the source sheets are as follows;

Name
- =JOIN(COLLECT({source sheet Range Name}, {source sheet Range ID}, ID@row, {source sheet Range Status}, "Enrolled", {source sheet Range Course A}, "Yes", {source sheet Range Course B}, "No"))
Surname
- =JOIN(COLLECT({source sheet Range Surname}, {source sheet Range ID}, ID@row, {source sheet Range Status}, "Enrolled", {source sheet Range Course A}, "Yes", {source sheet Range Course B}, "No"))
Status
- =JOIN(COLLECT({source sheet Range Status}, {source sheet Range ID}, ID@row, {source sheet Range Status}, "Enrolled", {source sheet Range Course A}, "Yes", {source sheet Range Course B}, "No"))
Course A
- =JOIN(COLLECT({source sheet Range Course A}, {source sheet Range ID}, ID@row, {source sheet Range Status}, "Enrolled", {source sheet Range Course A}, "Yes", {source sheet Range Course B}, "No"))
Course B
- =JOIN(COLLECT({source sheet Range Course B}, {source sheet Range ID}, ID@row, {source sheet Range Status}, "Enrolled", {source sheet Range Course A}, "Yes", {source sheet Range Course B}, "No"))

As you note, you just have to change the first cross-sheet reference of the COLLECT function like Name, Surname, Status, and so on.

You can use the INDEX(COLLECT()) combination, but in that case, you have to handle error cases, so I prefer the JOIN(COLLECT()) combination if I retrieve text data.

As @NickStaffordPM commented, it is much simpler to use a report. You just limit the access to the report to view only.😅

https://app.smartsheet.com/b/publish?EQBCT=535207c4b11c4d649a25dd479491c52f

LeoT

Hi jmyzk_cloudsmart_jp. Thanks for the reply! I'm happy to see I arrived at the same solution you proposed. The only difference is the usage of JOIN instead of INDEX. Unfortunately, the operations around these sheets are not so simple so I have some statements to respect:

access to the source file must be limited/blocked for the final users
we have a bunch of columns to add to the destination files where users have to insert some other student information
because of the initial block, users can't edit the destination file and consequentially they can't add info to the report

This is why I have to create a new sheet with all the required information of the source file and then create a filter connected to the destination files shared with the final users with editor permissions.

The initial filters are to obtain a shorter initial list, then I will use the report filters.

Does it sound correct?

jmyzk_cloudsmart_jp

@LeoT

It sounds correct if you are OK with showing other students' information. If I were in your position, the only concern is that students can see the other students' information. If you want to show information that belongs to a student, you can use the Dynamic View. (Not included in the core Smartsheet App. So, you need to purchase the App separately.)

Dynamic View showing only demo@cloudsmart.jp's information, filtered by the Enrolled-Yes-No setting; only other information fields are editable.

The source sheet with a contact list column (Email).

Restrict view by current user and restrict view by sheet filter.

Protect basic information with Read-Only.

LeoT

Interesting. This is my first step into Smartsheet so these are all more than welcome hints, thank you.

The sheet will be accessible only by staff members so I don't have this limit... I'll take a look at Dynamic View also.

Thank you.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.