Using file (sheet) name to look up and summarize multiple sheets?

Is it possible to create a summary sheet that uses a sheet name (or other unique identifier) lookup to pull data from a source file into a summary file?

I have a portfolio of projects in a workspace with a template folder for each project, which includes a "Project Summary" sheet, and a "Portfolio Summary" sheet that pulls date from each project's "Project Summary" data sheet. Some of the Project Summary data pulls from other sheets in the project template, some is entered manually. All of the data in the Project Summary sheets is stored in rows, whereas the Portfolio Summary is organized by columns (so I can't just copy a whole row from the source Sheet and paste into the destination sheet). For example:

Source Sheet (Project Summary):

Screen Shot 2022-03-04 at 5.13.45 PM.png

Destination Sheet (Porfolio Summary):

Screen Shot 2022-03-04 at 5.28.11 PM.png

To create the Portfolio Summary sheet, we are using "Link from Cell in Other Sheet..." and manually making the connections between the Portfolio Summary and each individual Project Summary to pull data from the latter to the former.

This method is A.) a time suck (technical term) and B.) prone to error (all those connections!!!).

To expedite this process and reduce exposure to errors, I would LOVE to build a procedure in my Portfolio Summary sheet that, provided a unique common identifier (project name? link to respective project's summary sheet?), goes to the respective Project Summary sheet and pulls the targeted data.

Is this possible?

Thanks

Jim

Find more posts tagged with

Accepted answers

Jim A

Hi @Genevieve P.,

This was very helpful and did get me part way. While it doesn't fully solve for the challenge I have here (Simplifying the creation of a Portfolio summary by automating the roll up of data from Project Summaries), it does help me streamline the process somewhat and does expand my knowledge of Smartsheet. All good stuff! Thank you!

I think to fully implement what I have in mind might require Control Center or perhaps external automations, such as those available via Microsoft's Power Automate. Or, maybe I'm missing something that's possible within Smartsheet.

Likely, I wasn't entirely clear in my original post about what I want. Now that I understand INDEX(MATCH), what I envision is having the Source Sheet that is referenced in the INDEX(MATCH) formula be dynamic and change based on a value entered into the Portfolio Summary sheet (the Destination Sheet). So, let's say I know the Project name and type that into the "Project Name" field in the Portfolio Summary, the INDEX(MATCH) formula for that row would reference the appropriate "Project Summary" source sheet for that Project and the rest of the fields in that row would autofill accordingly.

This may not be the best flow, but it's how my mind is mapping the process:

An array function would gather all of the filenames in a Workspace that include "Project Summary" in the file name.
The array would reside, perhaps, in a helper sheet that contains the filename, filepath, and the project name, which is extracted from the file. Or, maybe this data automatically sync's to the Portfolio Summary sheet.
If it resides in a helper sheet, the Portfolio Summary would reference that sheet to build an array of all projects names as values in a dropdown menu for the Project Name field.
Regardless of how the key identifier gets to the Portfolio Summary sheet, once there it becomes the lookup reference by which the appropriate "Source Sheet" name is inserted into the INDEX(MATCH) formula.

Whether or not anything like this is even possible with Smartsheet, I have a much better understanding of INDEX(MATCH), so Thank you!

~ Jim

All comments

Genevieve P.

Hi @Jim A

Yes! There's a way to do this with Cross Sheet Formulas instead of using cell-links. As long as your unique identifier matches across sheets, you can pull back matching information.

In your instance, it looks like you want to bring back data across one row instead of down a column, like in the source. What I would do here is create a top row in this destination sheet which lists out the values you're looking to bring back... so in your example I would have "Project Name" in the top cell of the Project Name column, then "Project Description" in the top cell of the Description column. In some cases it may simply be duplicating the column name, but the cell value has to match between the sheets for the formula to find what you're looking for.

Screen Shot 2022-03-08 at 10.48.16 AM.png

Once you have a top row with the matching values, you can use an INDEX formula to MATCH the value in the top row to the value down your first Project Summary column, then bring back the value next to it in your Description column.

Ex:

=INDEX({Second Column Source Sheet}, MATCH([Project Name]$1, {First Column Source Sheet}, 0))

First range in the formula is the column to bring info back from:

Screen Shot 2022-03-08 at 10.49.22 AM.png

Then you select the row above to reference what we're matching:

Screen Shot 2022-03-08 at 10.49.40 AM.png

Then we select the column that has the matching value in the source sheet:

Screen Shot 2022-03-08 at 10.51.03 AM.png

Finish the formula with the 0 (so it sees the list as "unsorted" and will search through each cell for the match), and voila:

Screen Shot 2022-03-08 at 10.51.19 AM.png

Once you have that set up, drag-fill over to the right:

Screen Shot 2022-03-08 at 10.52.40 AM.png

This should update [Project Name]$1 to now be Description$1 as you drag it along the other columns.

Then when you copy the folder where these templates are stored, you can choose to make sure the cross-sheet references stay stuck on to the newly created sheets instead of referencing the original template sheets (although this works with cell-linking as well). As you add new data to the source sheet, all you'd have to do is create the new, matching column in the other sheet, add in the cell data to the top row, then drag the formula across.

Let me know if this makes sense and is what you were looking to achieve!

Cheers,

Genevieve

Jim A

Hi Genevieve, This looks fantastic. I am working on this project this afternoon and will try to implement your advice. I'll circle back to let you know how it worked out. Thank you!

~ Jim

Jim A

Hi @Genevieve P.,

This may not be the best flow, but it's how my mind is mapping the process:

An array function would gather all of the filenames in a Workspace that include "Project Summary" in the file name.
The array would reside, perhaps, in a helper sheet that contains the filename, filepath, and the project name, which is extracted from the file. Or, maybe this data automatically sync's to the Portfolio Summary sheet.
If it resides in a helper sheet, the Portfolio Summary would reference that sheet to build an array of all projects names as values in a dropdown menu for the Project Name field.
Regardless of how the key identifier gets to the Portfolio Summary sheet, once there it becomes the lookup reference by which the appropriate "Source Sheet" name is inserted into the INDEX(MATCH) formula.

Whether or not anything like this is even possible with Smartsheet, I have a much better understanding of INDEX(MATCH), so Thank you!

~ Jim

Genevieve P.

Hi @Jim A

Thank you for elaborating further! You're correct, there currently isn't a way for a formula to automatically recognize a newly created sheet. You would need to re-create the cross sheet references to point to a new sheet once it has been created (although you'd only have to do this once per-reference).

Cross sheet references use the Sheet ID and Column ID to map to when you select them, which means that it's not based on a sheet name either (since the name could change). Because of this, sheet names cannot be used in a formula as the reference point.

This means that even if you had the Sheet Names listed in a dropdown somewhere, a formula wouldn't be able to read this value and know that it should update {where these go} based on that text. Does this make sense?

You're also correct that Control Center can automatically provision projects and create summaries based on that new project. It sounds like that may be the best route for you down the line as you continue to build out your process!

Cheers,

Genevieve

Jim A

Hi Genevieve,

Recreating "the cross sheet references to point to a new sheet once it has been created (although you'd only have to do this once per-reference)" is exactly what I've done, which while not the silver bullet, has a been a BIG help.

And, thank you for the tip re cross sheet references using Sheet ID and Column ID, and not Sheet Name. That certainly makes sense.

Thanks again for your help!

~ Jim

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.