Pull in data from another sheet

Options

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.

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    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.

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • gunnell
    gunnell ✭✭
    Options

    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).