Creating absolute reference to different sheet in INDEX MATCH

[MoreGone]

I might be approaching this completely wrong. Please feel free to scold.

In my workbook I have a sheet called Master Tracker for tracking all things including installation dates. Weekly we receive an updated file from another team called Weekly Updates that has install date changes. What I have done is Copy the Weekly Updates sheet to my Workbook and then use INDEX MATCH to search for new dates. All that works well.

=INDEX(‘Weekly Updates'!E$2:L$490,MATCH('Master Tracker'!A2,’Weekly Updates'!L$2:L$490,0),1)

What I would like to do is be able to delete or rename the Weekly Updates worksheet when I get updated information and move the new sheet to my workbook with the Master Tracker and have the existing INDEX MATCH formula continue to work. What I am finding is that when I delete the sheet, my INDEX MATCH formula changes to #REF looking for the sheet. I would like it to remain so that when I add the new sheet the formula still works.

If I try to rename the Sheet in advance, it updates the reference to ‘Weekly Updates (2)’ and when I add my new Weekly Updates sheet its too late and I have to update the formula again.
How do I make a static call to a sheet that might be deleted or renamed?

Trying to avoid
=INDEX(#REF!E$2:L$490,MATCH('Master Tracker'!A2,#REF!L$2:L$490,0),1)
or
=INDEX(‘Weekly Updates (2)'!E$2:L$490,MATCH('Master Tracker'!A2,’Weekly Updates (2)'!L$2:L$490,0),1)

Thanks

 

[Eric W]

Try:

=INDEX(INDIRECT("'Weekly Updates'!E$2:L$490"),MATCH('Master Tracker'!A2,INDIRECT("'Weekly Updates'!L$2:L$490"),0),1)

However, note that the ranges within the quotes within the INDIRECT do not change as you drag/copy the formula. If that's necessary, we need a bit more work.

 

[MoreGone]

Try:

=INDEX(INDIRECT("'Weekly Updates'!E$2:L$490"),MATCH('Master Tracker'!A2,INDIRECT("'Weekly Updates'!L$2:L$490"),0),1)

However, note that the ranges within the quotes within the INDIRECT do not change as you drag/copy the formula. If that's necessary, we need a bit more work.

The only thing I needed to change as I drag/copy the formula was the A2 cell to A3, A4 and so on, which is working perfectly. Thank you!

 

[Eric W]

Glad to help!