Reference a cell from another sheet

Options

I am trying to reference a specific cell from one sheet in another. I've found formulas for reference a range of cells, but I just want one particular cell. Can anyone help?

Best Answer

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭
    Answer ✓
    Options

    Start to build your formula and you should see the reference another sheet in the helper box. Just click on that and then find your sheet you are looking for that has the cell you want to reference. Just click on the specific cell then click on the blue button in the bottom right corner of the window. You have now created a reference to a single cell within a formula.

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

Answers

  • liz.mayeux
    liz.mayeux ✭✭✭✭✭
    Options

    have you tried linking the cells? That works best if it is a static entry, but if you are needing ranges of cells to pull back information from a unique entry. INDEX/MATCH is the best formula to use.

  • David Dolch
    David Dolch ✭✭✭✭✭
    Options

    To cross reference a single cell, you just click on that single cell. What is happening when you attempt to do so?

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭
    Answer ✓
    Options

    Start to build your formula and you should see the reference another sheet in the helper box. Just click on that and then find your sheet you are looking for that has the cell you want to reference. Just click on the specific cell then click on the blue button in the bottom right corner of the window. You have now created a reference to a single cell within a formula.

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

  • Ken Armstrong
    Ken Armstrong ✭✭✭✭✭✭
    edited 05/23/23
    Options

    @Angie Little If you just want cell to cell info and not a Vlookup range, you can also right click on the cell you want the data in and select the link from cell in other sheet option see image. This will allow you to select the sheet and single cell you want to reference.


    Ken Armstrong

    Smartsheet Operations Analyst, Carelon Medical Benefits Management

    Certified Smartsheet Administrator

    Be Firm! Be Fair! Be Friendly! Be Honest!!!

  • WinaHath
    WinaHath ✭✭✭✭
    edited 06/13/23
    Options

    I have a sheet that contains the Employee data and another sheet that requires that data.

    From Collaborators List:

    to Requisition Management Sheet

    please help me generate the formula.

    Thanks!

  • Ken Armstrong
    Ken Armstrong ✭✭✭✭✭✭
    Options

    @WinaHath This one is easy but takes a bit longer as it will have to be a VLOOKUP. With a VLOOKUP you will need to create a reference to the first sheet with the employee id as the unique identifier.

    Ken Armstrong

    Smartsheet Operations Analyst, Carelon Medical Benefits Management

    Certified Smartsheet Administrator

    Be Firm! Be Fair! Be Friendly! Be Honest!!!

  • Ken Armstrong
    Ken Armstrong ✭✭✭✭✭✭
    Options

    @WinaHath I can walk you through it so you can learn. Do you have zoom or teams where you can screen share?

    Ken Armstrong

    Smartsheet Operations Analyst, Carelon Medical Benefits Management

    Certified Smartsheet Administrator

    Be Firm! Be Fair! Be Friendly! Be Honest!!!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 06/13/23
    Options

    Hi @WinaHath

    I hope you're well and safe!

    I'd recommend an INDEX/MATCH combination. Here's the structure.

    =INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row,{ColumnWithTheValueToMatchAgainsTheCell}, 0))

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

  • WinaHath
    WinaHath ✭✭✭✭
    Options

    @Andrée Starå IT WORKED!!!!! THANK YOU SO MUCH!😁

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

    @WinaHath

    Excellent!

    Happy to help!

    Remember! 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.

  • KateP721
    KateP721 ✭✭✭
    Options

    @Andrée Starå Thank you!! I was trying to do something along these lines and knew that a VLOOKUP or INDEX would work but for some reason I wasn't getting the right data in the right order. I've been searching for an answer for two days - even had our Smartsheet rep tell me we likely needed "DataMesh" to do this. I was pretty sure I had done something very similar already and just had to get the formula right and it would work.

    Your replacement verbiage of "ValueYouWantToShow", "ValueToMatch" and "ValueToMatchAgainst" was an incredibly easy way to plug in my data to the formula how it needed to be and it worked immediately.

  • Angie Little
    Options

    @Ken Armstrong

    I am trying to link multiple cells in one sheet to one cell in another sheet. I've successfully linked each individual cell to the one cell in the other sheet, but doing this individually is time consuming. If select the cell range, it only seems to link in the first cell in the range. Do you know if there is another way to link multiple cells to this one cell from my other sheet?

  • WinaHath
    WinaHath ✭✭✭✭
    Options

    @Andrée Starå

    What is wrong with my formula? I copied this from the Approver column (which works) and changed it to match what I want to output to my sheet :

    copied from:

    =IFERROR(INDEX(COLLECT({Approver}, {Department}, @cell = [Select the Department Code for this request]@row, {Approval Sequence}, @cell = 1), 1), "")

    to:

    =IFERROR(INDEX(COLLECT({Dollar Threshold}, {Dept Code}, @cell = [Dept Code]@row, {Approval Sequence}, @cell = 1), 1), "")

    I get an #INCORRECT ARGUMENT error

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

    Hi @WinaHath

    I hope you're well and safe!

    Can you share some screenshots of the formula in your sheet? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

    I hope that helps!

    Be safe, and have a fantastic weekend!

    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.

  • Genevieve P.
    Options

    Hey @WinaHath

    Just a note - in your first image you have one extra ) at the end of your formula, versus what you have copy/pasted here in the forum.

    Is that the formula you're using? If so, remove the extra ) at the end:

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!