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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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: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.
-
@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!!
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!