How To reference cells from another workbook based on path value in cell

[kripper]

Thanks in advance for anyone that can assist.

I am trying to make this formula work, and I continue to receive #REF errors.

=INDEX(INDIRECT("'"&SETUP!$C$59&"[PPM Dashboard.xlsb]DATA1'!$K$3:$K$32"),MATCH($C3,INDIRECT("'"&SETUP!$C$59&"[PPM Dashboard.xlsb]DATA1'!$C$3:$C$32"),0))

The path it refers to changes based on computer using it, so it needs to pull from a cell in the workbook.

It have confirmed the drive path and the second sheets file name, sheet name, and cell reference, and they are all correct, however I cannot seem to make this formula work.

Any help would be greatly appreciated.

 

[JustynaMK]

Hi kripper,

It seems like there is an issue with Excel 2013:

https://answers.microsoft.com/en-us/msoffice/forum/all/excel-2013-external-links-not-updating-unless/c69595d3-3993-495f-8593-1a07db3574bf

Your external file (PPM Dashboard.xlsb) needs to be open in order to have this formula working. Removing INDIRECT formula and replacing it with an actual path also returns the correct result:
=INDEX('C:\myfolder\[PPM Dashboard.xlsb]DATA1'!$K$3:$K$59,MATCH(C3,'C:\myfolder\[PPM Dashboard.xlsb]DATA1'!$C$3:$C$59,0))

However, this does not solve your issue, as you want your path to be dynamic (i.e. cell referenced). I couldn't find any appropriate fix, but hopefully this will give you some clarification and maybe someone else on the forum will post a correct solution. Sorry!

 

[mikerickson]

The INDIRECT function does not work with closed workbooks and never has.