External links, with Index or Vlookup Funtions

[MarcoMagal]

Hello,

Good day to all.


I'm trying to automate some Sheets and I have a old problem with external links


I have this formula


INDEX('C:DC[Sales.xlsm]Elite'!$A$14:$X$439;$A28;T$12)


I have already try to create a range name like


FullPath='C:DC[Sales.xlsm]Elite'!$A$14:$X$439


Then try to make it work like INDEX(FullPath;$A28;T$12)


Is it possible to work around this? I'v try also with indirect Funtion.

Many thanks


Marco

 

[Faseeh]

Hi MarcoMagal,


I just tried this with one placing a range in a workbook, then closed it and use following formula to retrieve values from the closed workbook, and it worked:


=INDEX('C:Documents and SettingsRizwan.zafar.MILLDesktop[Daily Shutdown Report (May, 12) Mills 02.xlsx]Sheet2'!$A$1:$A$7,C1,0)


Can you specify what problem you are facing?


Faseeh

 

[MarcoMagal]

Hello Faseeh


I dont have problems with this formula


=INDEX('C:DC[Sales.xlsm]Elite'!$A$14:$X$439;$A28;T$12)


I'm trying to create a way to include a variable representing the path, file and range


ex:


Path='C:DC

File=[Sales.xlsm]

Product=Elite

Range=$A$14:$X$439


Then I could use FullPath=Path&File&Product&"'"&Range


and use it in the Formula


=INDEX(FullPath;$A28;T$12)


But Excel don't accept that.

Thanks

 

[NARAYANK991]

Hi Marco ,


The following works , but only if the file which is the external link is open :


=INDEX(INDIRECT("'"&Path_Name&"[&File_Name&]"&Sheet_Name&"'"&"!"&Range_Name),$A28,T$12)


With reference to your example :


Path_Name - C:DC


File_Name - Sales.xlsm


Sheet_Name - Elite


Range_Name - $A$14:$X$439


Narayan

 

[MarcoMagal]

Hello Narayan,


Thanks for the reply,

But I need a solution with those files Closed.


Regards,

Marco

 

[NARAYANK991]

Hi Marco ,


I think that may not be possible using formulae ; since you are creating a text string for the path , file , sheet and range names , the only way to use it within the INDEX function is using the INDIRECT function ; the INDIRECT function does not work with closed files.


You may have to use VBA for this.


Narayan