Indirecet on Closed files

Omer_K

Board Regular
Joined
Apr 9, 2017
Messages
124
Office Version
  1. 365
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.
 
out of curiosity
is that what you want to achieve?
fromfolderwithnames.png
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.
 
Upvote 0
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.
 
Upvote 0
Probably this is the only solution (INDIRECTEX) ..
Mark I really thank you for all the effort - you are the best! :)
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top