Index, Match, Collect?

Hello Community,

I am hoping someone has a suggestion for my issue. I searched the previously asked questions and couldn't seem to find it. Ultimately, I am trying to maintain one master sheet of data that populates secondary filtered sheets based on their "Area of Expertise". I realize I can do this using the reporting function; however, as far as I can figure out you can't use card view on a report. And the client I am hoping to build this for really wants to view the data in card view. So, what I am trying to figure out is a way to produce additional sheets, (while still maintaining the data on one sheet for updating purposes and viewing), that are automatically found and linked. So if a new entry is added to the data sheet, the secondary sheets will be updated with that entry as well.

The above data is an example of what I would like to search from to populate the secondary sheets. I would like to use the work group tag to designate the data to be pulled to the second sheet. I am also hoping it is possible to pull the cell links over as well.

I am sure this is as clear as mud right now.. But hopefully someone has the knowledge to prevent a lot of tedious busywork on my part! I am not completely married to this idea, so if anyone has a better suggestion I am all ears!



  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Shesha_K

    A Report would be the only way I can think of which would keep the data "live", and automatically update as you change this master sheet.

    You could use the Copy Row automation to copy over rows to specific sheets based on criteria (see here). This would create a duplicate of the row on the sub-sheet, but any formulas and cell links in the row will be replaced with static values in that destination sheet so it wouldn't be live. You would need to manually go in and create those cell links... or you could set up multiple formulas in that destination sheet in new columns to auto-fill, but this may be quite a bit of work.

    Do they need to see the master sheet in Card view as well? If they would be willing to have the one, Master sheet as grid view, then this could be the Report looking at the Card view Sheets. Since you can make updates to most row data in Reports, this would pull through to all the sub-sheets and would be the easiest option.

    In the meantime, please submit an Enhancement Request to have Card View in Reports!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!