VLOOKUP or INDEX MATCH Referencing Another Sheet

I would like to pull "Type" from a different sheet by matching the Project #. I've used both INDEX MATCH and VLOOKUP formulas which has worked well!

Here are the formulas:

=INDEX({Program Intake Form Range 1}, MATCH([Project #]#, {Program Intake Form Range 2}, 0))

=VLOOKUP([Project #]#, {Program Intake Form Range 2}, 5, false)

Problem Statement: But the issue is when I copy these formulas into another sheet, I am getting #INVALID REF error. I am able to fix this issue by editing "Reference Another Sheet" and manually re-selecting the columns from the other sheet.

Is there a way to write the formula, so that I would not need to manually Reference Another Sheet?

I have over 100 sheets (and this will grow over time) and I want to automate this formula.


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 05/11/23 Answer ✓

    Hi @Omid

    Cross-sheet references, such as your {Program Intake Form Range 1}, are unique to each individual sheet where they are created.

    This means that when you copy/paste a formula with a reference in it into an entirely new sheet, the reference is just plain text without being associated with another sheet or column. You will need to manually tie in the correct column to the referenced text each time, as you've been doing.

    Cheers,

    Genevieve

Answers

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

    Hi @Omid

    I hope you're well and safe!

    You would have to use column-formulas in the other sheet for it to work.

    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:[email protected] | P: +46 (0) - 72 - 510 99 35

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

  • Omid
    Omid ✭✭
    edited 04/26/23

    @Andrée Starå - Hope you're having a great week! Thank you for your quick response!

    By that, do you mean putting the formula in a cell within the sheet versus Sheet summary?

    If so, I tried that by just copying "=INDEX({Program Intake Form Range 1}, MATCH([Project #]#, {Program Intake Form Range 2}, 0))" into Sheet #2 and I'm getting the same error. See below.

    But When I go into sheet #2 and manually click "Reference Another Sheet" and select the reference sheet and reference column in the other sheet, then it works:

    Wondering if there is a way to just copy this formula and paste it in Sheet #2, 3, 4, etc in an automated way?


    Thank you in advance!!

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 05/11/23 Answer ✓

    Hi @Omid

    Cross-sheet references, such as your {Program Intake Form Range 1}, are unique to each individual sheet where they are created.

    This means that when you copy/paste a formula with a reference in it into an entirely new sheet, the reference is just plain text without being associated with another sheet or column. You will need to manually tie in the correct column to the referenced text each time, as you've been doing.

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!