Indirecet on Closed files

[Omer_K]

Hey everyone,
In Google searches there seem to be a number of solutions but I didn't find anything creative ..
I have a file with an indirect function linked to different files,
When the external file is open everything works perfectly but when the external files are closed (there are more than one external file) the INDIRECT function returns a ref error.
anything can help

Omer.

 

[sandy666]

out of curiosity
is that what you want to achieve?

 

[Omer_K]

out of curiosity
is that what you want to achieve?
View attachment 4972

Hey,
I tried to do this using the Power Query,
The problem is that the files are overloaded with data and then everything crashes
What I sent is just an example and not the real files

 

[sandy666]

it was just a question without suggesting anything ?

 

[Omer_K]

it was just a question without suggesting anything ?

Thank you Sandy

 

[sandy666]

You are welcome

 

[MARK858]

The IndirectEx function works fine for me with the formula below in B2....

=IndirectEx("'"&$E$2&"["&$E$3&$E$4&".xlsx]Costs'!"&ADDRESS(ROW(B2),COLUMN(B2)),TRUE)

Remember that you need both the IndirectEx function & the Private Sub GetNames in the code module.

 

[Omer_K]

You're right it works
But it's really really slow

 

[MARK858]

But it's really really slow

Not much we can do about that I am afraid with UDF's.... afraid they are slower than native functions and you pay a price for making Excel work harder by dealing with closed workbooks.

 

[Omer_K]

Probably this is the only solution (INDIRECTEX) ..
Mark I really thank you for all the effort - you are the best!

 

[MARK858]

You're welcome