Force excel to keep sheet name when referencing external tables

[jonagpa]

Hello!

Is there a way to force excel from automatically "rewriting" the address of the external reference?

Example
When I mouse over and select a table in another workbook as a source, the formula bar shows this:

=[WorbookName.xlsx]SheetName!TblName

Once I hit enter, the formula bar shows this:

=WorbookName.xlsx!TblName[#Data]

Brackets and sheet names get removed and [#Data] gets added.

I confirmed that excel understands the address with the sheet name by using indirect

INDIRECT([WorbookName.xlsx]SheetName!TblName)

So its not a limitation by excel.

If anyone wants to know why I need this, I created another thread where I documented why and how I attempted to fix my issue. I went into a lot of detail and I think it was simply too long and over-complicated as I received 0 replies. Its still good info in case someone is doing research but I created this to simplify my question. If you want to have a look: https://www.excelforum.com/excel-gen...ng-errors.html

Really hoping to find a solution, thanks in advance!

[AliGW]

Administrative Note:

Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here.

Please see Forum Rule #5 about thread duplication.

I am closing this thread, but you may continue here in the original thread: https://www.excelforum.com/excel-gen...ng-errors.html