O resultado disso é como é um endereço legítimo em um link entre arquivos:
="'"&CELL("filename",A1)&"'!$B$3"
(você pode colar isso como resultado, adicione um = na frente e ele funcionará bem)
Exceto concatenar ou indireto, não queira aceitá-lo. Se você quiser ver a coisa real - crie dois arquivos, copie uma célula de um link para colar e feche o arquivo de origem.
Existe alguma maneira de enganar o uso do texto como caminho?
Eu faço desta maneira - porque eu sempre posso determinar onde os outros arquivos são baseados em onde o arquivo atual está. Portanto, qualquer redirecionamento é uma simples questão de substituir o texto.
Estou tentando evitar o VBA atm.
Engraçado, ele funcionará para criação de endereços no arquivo:
=HYPERLINK(CONCATENATE("#'"&RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1))&"'!","A5"),"FFS")
(com isso, clique no texto do resultado da célula para pular para A5)