#NO MATCH Error on INDEX-MATCH

Nadine_DL

Hi,

I have a Master Project Directory (MPD) that is a consolidated register of all projects registered with each company associated to our Group of Companies - each with their own project numbering system. Because of this, I created different Auto-numbering Sheets (ANS), each with their own autonumbering column.

Workflow: Form completed by Project Manager - Loaded to MPD - MD's Approval - Row copied to the relevant ANS - Project number generated in ANS - Project number INDEX-MATCHED from ANS back into MPD

Note: The reference used to match is the {Created date} - because all other information is subject to change and adding an autonumbering column in the MPD will override the autonumbering column in the ANS.

This has been running without issue for 2 years until recently. The approval of one project somehow triggered a #NO MATCH error on the project number for that project and then seemed to have caused all projects added after to have this same issue.

The error lingers until I log into the sheet and refresh. But then it comes back when an automation runs and then I have to refresh again to remove the error. When I check the matching reference in both sheets, it is the same. Both are values that cannot be manually changed because they are generated by the system {Created date}.

Could different regional settings affect this somehow - formatting of date? If so, why is it only causing this issue now?

BullandKhmer

Hi,

I've experienced this in the past and YES regional date formatting can cause this. Specifically the date formatting in the settings of the last person to access the sheet.

The best solution would be for all Americans to wake up, smell the flowers and switch to a date format that makes logical sense, like the rest of us.

Nadine_DL

Thank you, @BullandKhmer!

At least now I have identified the issue. Now, for looking into an alternative solution.