This is a pop-up that's sparked multi-minute-long rants which always result in a profanity-laden diatribe against seemingly arbitrary rules in general and Microsoft in particular. When I joined the company, it was with a mandate to figure out why this is the case and fix it. I'm happy to report that I've determined the answer to the first part and very good reasons why neither I nor anyone else is going to do the second.
It all has to do with naming qualification and ambiguity resolution. Let's say you have two workbooks, BookA.xlsm and BookB.xlsm. If BookA wants to reference a cell in BookA, it can simply set one cell equal to another using the familiar =[cellref] syntax as shown below:
What if you want to reference a cell in BookB? Well that's easy, just qualify it with the name of the workbook and worksheet where Excel should go to find the cell in question as shown below:
This is a great feature of Excel which has enabled people to build up tremendously complicated systems of workbooks with simple cross-references, multi-workbook calculation chains, and encapsulated functional areas. You can even follow standard software development practices like Model-View-Controller patterns by having a Model workbook encapsulating the business logic and data, a View workbook encapsulating the UI and results display, and a Controller workbook managing the interactions. As you jump into VBA programming, ties between workbooks will only get more complicated and qualification more necessary.
But wait!! What happens if there are two workbooks named BookB.xlsm open in the same Excel process? Which workbook is meant by BookB.xlsm? The answer is that you can't know. The duplicated use of the same name has made this case strictly ambiguous.
What can be done about that? You have two options 1.) Redesign the qualification scheme such that it is completely unique, or 2.) Disallow multiple workbooks with the same name to be opened by a single Excel process. Obviously the later option was chosen by the Excel team way back in the early days of Excel.
This is why multiple workbooks with the same name cannot be opened by Excel within a single process.
If that sounds unfortunate, consider option 1. To come up with a qualification scheme that's unique, you'd need to detach the workbook name from the qualifier. Let's say you choose a GUID-based system where each GUID is associated with a particular workbook file and that you know about all the workbooks you'll need and all their GUIDs. Now you write a big complicated workbook system with tons of GUIDs all over the place. What happens if someone makes some changes to an existing workbook and saves a copy of it. Either these two copies now have different GUIDs or the same GUID, both of which would introduce problems down the line.
The only reason that multiple workbooks can reference each other is that Excel supports a Multi-Document Interface (MDI) with multiple workbooks managed by a single process and UI. So the natural cure to the resulting reference ambiguity is to disallow it by opening each workbook in a separate instance of Excel. This can easily be done by opening Excel from the commandline or start menu and then opening workbooks from within that interface.
That's all on this topic. Hopefully this at least helps you to feel informed the next time you forget and try to open a second workbook with the same name and get that lovely warning.