Formula Changing when sorted

[MoreGone]

I am using an INDEX MATCH with INDIRECT to pull in Install Dates from another sheet based on Account Numbers on both sheets

=INDEX(INDIRECT("'Master list'!F$2:F$492"),MATCH('Master Tracker'!$A50,INDIRECT("'Master list'!K$2:K$492"),0),1)

We get the updated dates weekly on a sheet called Master list. So my approach was to add the Master list sheet my workbook with Master Tracker using INDIRECT so I could remove the Master list and replace it and the formula still work. And it is working fine. Until I sort.


I am putting the above formula in B50, right next to A50, the account number. Once I sort, the reference to the Account Number changes independently of the Account Number. So the account number in A50 when sorted becomes A60, but the reference in B60 is somehow updated to reference against A67. Any idea where I am going wrong?

 

[Fluff]

Try removing the reference to the sheet containing the formula.

 

[MoreGone]

Try removing the reference to the sheet containing the formula.

Sorry, I don't believe I am following. I am not referring to a sheet that contains a formula.

Master list is sent to me at the end of the week with all the scheduled Install Dates next the account #. I am adding the install dates to my Tracker that contains a lot of other fields. Master list does not have any formulas.

On my Master Tracker, I am looking at Master list to find the additional accounts that were scheduled install dates. It really is just a INDEX MATCH to another sheet. But I have added INDIRECT into the formula so I can simply delete the Master list sheet weekly when I get the new version. I believe my troubles are stemming from the use of INDIRECT but really uncertain how as the reference to the Account Number in the A column is not inside the INDIRECT function

 

[Fluff]

Is the formula on the sheet called "Master Tracker"?

 

[MoreGone]

Is the formula on the sheet called "Master Tracker"?

Thank you. I have removed the Reference to Master Tracker and it is working as intended. Do you by chance know why this would cause this? When writing the formula and flipping between the tabs Excel included that for me. I saw it but figured it was just 'extra' or 'the long name' and wouldn't have an impact. Boy was I wrong.

 

[Fluff]

Glad it's sorted & thanks for the feedback

I've no idea why having the sheet name in causes problems when sorting, I just know it does.