Pull in data from another sheet

gunnell

I'm new to smartsheet and considering moving over from Airtable. I have individual sheets for each project that is currently in development. Each of these sheets follows the same template where a specific cell calculates the percentage of completion for that project in real time.

In another sheet ("Summary Sheet"), I have general information about each project. I'd like to pull in that single cell from each project that gives an overall percentage of completion but I haven't been able to get that to work. Additionally, I would really like to find a way to reference another sheet's name based on the text in the primary field of the summary sheet.

Example:

The circled cell below is what I'm trying to pull into another sheet. The 58% is created using a formula averaging the percentages in the indented rows below it.

"Apple Project range 1" is referencing the single cell circled in the above screenshot.

The result in the "Summary Sheet" I keep getting is "1". I've tried formatting as a percentage but the result is 100% (rather than the 58% I'm expecting).

I've tried doing this using the cell linking feature as well but get the same exact result, "1". I considered that it might be a setting where it was rounding my 58% up to 100%, but I tested it by setting the completion statuses to 0% in the Apple Project sheet and still got "1" in the Summary Sheet.

My second question is, how can I find that information in another sheet that has a title matching the primary column for that row in the Summary Sheet. In other words, I also have a sheet called "Banana Project". In the "Summary Sheet", I'd like to have that "% Complete" column go find cell 1B in a sheet titled whatever is in the Primary Column (in this case Banana Sheet) so I don't have to individual link every sell. We have a lot of projects/sheets that we'd be trying to summarize here. I hope that makes sense.

Thanks in advance.

Andrée Starå

Hi @gunnell

I hope you're well and safe!

Strange that it doesn't work when you're cell-linking.

I'd be happy to take a quick look. (share too, andree@workbold.com)

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

https://help.smartsheet.com/function/vlookup

To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the Destination sheet.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

gunnell

Hi Andree, I've shared a workspace with you with sample sheets in it. As you'll see on the "Summary Sheet", I was able to get it to work using VLOOKUP, but I'd really like to find a way to automate the formula a little more by somehow referencing the primary column name and matching that to the Sheet title rather than creating a custom range in the formula for every new project (there are going to be a lot of projects).