Reference/Link Cell Data from one sheet into another sheet's Summary

Michelle S.
Michelle S. ✭✭
in Formulas and Functions

Need an example of how to link or reference the value from a cell in one sheet (Project Intake Portal) into a field in the Summary of another sheet (Future New External Vendor Template)?

ReferenceSheet1DataInSheet2Summary.jpg


· Share on FacebookShare on Twitter

Best Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Michelle S.

    Will this always be the top row of the sheet? If so, we could use an INDEX Function to bring back this cell:

    =INDEX({Backlog Status Column}, 1)

    The 1 at the end says what row to bring back from that column. Let me know if this makes sense and will work for you!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Michelle S.
    Michelle S. ✭✭
    Answer ✓

    THANK YOU @Genevieve P. ! 👍️ This gave me enough to get there...

    I just needed a solid example for creating/using references and the formula needed (INDEX). Your example above looks like it's referencing the column name not the sheet name but it is absolutely correct.

    1. Create sheet range reference for Sheet: Project Intake Portal, Row: the project row (in example below it is named Project Intake Portal Sample Project)
    2. Using the INDEX function reference the Range Project Intake Portal Sample Project, the Row 1, and the Column that the Backlog Status data is in 5
    3. The formula ends up looking like this... =INDEX({Project Intake Portal Sample Project}, 1, 5)


    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Michelle S.

    I'm glad you're having fun! I always have fun in Community, I learn something new every day. 🙂

    I have to admit I'm not quite sure I fully grasp your set-up or bigger picture. It's likely something that would be easier to visualize than write out, but I'm sure we can figure out a solution based on the information provided.

    If you want the INDEX function to pull back a cell from a specific row based on a matching project name, I would use an INDEX(MATCH in this instance.

    The MATCH part of the formula would reference the cell in your current Template sheet that has the Project Name, and then it would look into the column in your Project Intake sheet to find the row that has that same Project Name. Ex:

    =INDEX({Column with Value to Return}, MATCH([Project Name]$1, {Project Name Column}, 0))

    The Match find the associated row, and then the Index pulls back the cell from the specified column. These two ranges would be full column ranges, so that as new projects are added and new templates created it would look through each row to find the correct match and bring that back. It also means that you can shuffle the columns around or add extra and the formula will still reference the correct column.

    Is this closer to what you're looking for?

    Cheers!

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter

Answers

  • Stacy Bobbitt
    Stacy Bobbitt ✭✭

    I just got done doing this. I had to do a countif so if thats what you're try to do, it should look something like this.

    =COUNTIF({Backlog Status List Range}, "Requested")

    When you write the formula =COUNTIF( click on reference another sheet, find the sheet, then highlight everything in the backlog Status column.

    · Share on FacebookShare on Twitter
  • Michelle S.
    Michelle S. ✭✭

    Thank you Stacy, I literally just want the Value 'Requested' to appear in the Sheet Summary.

    Here's the backstory if it helps. I have a sheet I capture all potential projects (Project Intake Portal sheet), once there is enough information for a project to go through a POC A project plan (Future New External Vendor Template) is created; ideally I'd like to pre-populate the Future New External Vendor Template sheet summary with information already captured in the Project Intake Portal sheet.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Michelle S.

    Will this always be the top row of the sheet? If so, we could use an INDEX Function to bring back this cell:

    =INDEX({Backlog Status Column}, 1)

    The 1 at the end says what row to bring back from that column. Let me know if this makes sense and will work for you!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Michelle S.
    Michelle S. ✭✭
    Answer ✓

    THANK YOU @Genevieve P. ! 👍️ This gave me enough to get there...

    I just needed a solid example for creating/using references and the formula needed (INDEX). Your example above looks like it's referencing the column name not the sheet name but it is absolutely correct.

    1. Create sheet range reference for Sheet: Project Intake Portal, Row: the project row (in example below it is named Project Intake Portal Sample Project)
    2. Using the INDEX function reference the Range Project Intake Portal Sample Project, the Row 1, and the Column that the Backlog Status data is in 5
    3. The formula ends up looking like this... =INDEX({Project Intake Portal Sample Project}, 1, 5)


    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    Hi @Michelle S.

    I'm glad you were able to get something that works for you!

    I would recommend only selecting the one column needed in your Cross-Sheet reference though (instead of the whole sheet). It will help your formula run faster because then it doesn't have to look through all of the cells in the entire sheet to find the one you're looking for, and it means you'll be less likely to hit the cell-reference limit.

    =INDEX({Project Intake Portal Sample Project Backlog Status Column}, 1)

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Michelle S.
    Michelle S. ✭✭

    @Genevieve P.

    Absolutely, I tried to keep the selected range as small as possible by only grabbing the row I needed. In Step 1, the reference I created is only the row I would be working with. I then expanded the use of the INDEX function to grab the column I needed for each field I'm putting a formula in which resulted in my formula as follows:

    =INDEX({PIP Sample Project}, 1, 2)

    But... now I'm trying to figure out how to set this all dynamically because we all know over time we may get new columns added which will mess up the column numbers I'm referencing.

    Any ideas on how to capture a column number dynamically? I would like to replace the '2' with a formula or reference that would pull the column based on name and return me the column number needed for the INDEX formula. (Hope that makes sense)

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    Hi @Michelle S.

    Thanks for clarifying!

    You can actually get even more specific with a Cross Sheet Reference. Instead of grabbing the column or the row, select just the one cell as your reference (essentially a cell link). Then you don't need to add the column reference in your formula at all since there is only one column to bring back!

    =INDEX({One Cell}, 1)

    This will also keep it dynamic so as you move the column around or add new columns it will continue to reference that one cell.

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Michelle S.
    Michelle S. ✭✭

    Good Morning @Genevieve P.

    First, thank you again for taking so much time to work through this with me, I'm new to this but I'm hoping you are having as much fun helping me as I am learning Smartsheet. I have an extensive background in both database and application design and development so it's always exciting to learn a new product.

    I started with referencing a specific cell as well, however, my goal is to design a Template (Project Plan) that will pull information from the 'Project Intake Portal' sheet (a repository of projects, one row for each project). In the Sheet Summary of the Template I would like to dynamically reference the row and column number (ideally based on 'Project Name' which is in a cell in the first row of the Template) for each fields I'm pulling information from in 'Project Intake Portal' sheet. Hope this makes sense.

    Is there a way to dynamically capture a column number and a row number based on a value?

    https://community.smartsheet.com/discussion/comment/316254#Comment_316254


    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Michelle S.

    I'm glad you're having fun! I always have fun in Community, I learn something new every day. 🙂

    I have to admit I'm not quite sure I fully grasp your set-up or bigger picture. It's likely something that would be easier to visualize than write out, but I'm sure we can figure out a solution based on the information provided.

    If you want the INDEX function to pull back a cell from a specific row based on a matching project name, I would use an INDEX(MATCH in this instance.

    The MATCH part of the formula would reference the cell in your current Template sheet that has the Project Name, and then it would look into the column in your Project Intake sheet to find the row that has that same Project Name. Ex:

    =INDEX({Column with Value to Return}, MATCH([Project Name]$1, {Project Name Column}, 0))

    The Match find the associated row, and then the Index pulls back the cell from the specified column. These two ranges would be full column ranges, so that as new projects are added and new templates created it would look through each row to find the correct match and bring that back. It also means that you can shuffle the columns around or add extra and the formula will still reference the correct column.

    Is this closer to what you're looking for?

    Cheers!

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Michelle S.
    Michelle S. ✭✭

    Thank you @Genevieve P. - this guidance gave me exactly what I needed to create the dynamic link I was looking for! 🎉🏆

    Final Formula:

    =INDEX({Backlog Status},MATCH((INDEX((Task1), 1)), {Project Names},0))

    {Backlog Status} = a reference to the [Backlog Status] column in the Project Intake sheet (this reference changes depending on which column I am looking for data for)

    Task1 = the cell with the Project Name in it (which is actually just a reference to the cell in the Project Intake Sheet with the Project Name in it)

    {Project Names} = a reference to the [Project Name] column in the Project Intake Sheet

    Result (from example above): I get the value in the Sheet Summary of the Project Plan for the Backlog Status for the Project in my Project Intake sheet

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    Wonderful! I'm glad you were able to achieve the result you're looking for. 🎉

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!