VLOOKUP or INDEX MATCH Referencing Another Sheet

Omid

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 every time?

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

Note:

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?

Kelly Moore

Hey @Omid

Cross sheet references are sheet dependent and need to be inserted in every sheet - you cannot copy paste these formulas into new sheets

Kelly