LARGE function with external links

[lightwood]

I require a formula to calculate the 5th highest value in the same cell of a large number of workbooks. Each workbook contains a large amount of data and are too big to be combined within one file.

I have attempted to achieve this by using the large function with an array such as the following example but the formula returns an error due to the external links.

=LARGE({'C:\data\[data1.xlsx]sheet1'!A1,'C:\data\[data2.xlsx]sheet1'!A1,'C:\data\[data3.xlsx]sheet1'!A1,'C:\data\[data1.xlsx]sheet4'!A1,'C:\data\[data5.xlsx]sheet1'!A1,'C:\data\[data6.xlsx]sheet1'!A1,'C:\data\[data7.xlsx]sheet1'!A1,'C:\data\[data8.xlsx]sheet1'!A1},5)

Can anyone please confirm where my error is or a formula that will allow me to determine this value?

 

[Richard Schollar]

I think you'll need to bring each individual value back to its own cell in the workbook containing the formula (eg A1:A8) and then reference that range in the LARGE function if that makes sense (so each external link will occupy its own cell).