A few questions, err:504, alternative is really slow, etc

[King_ZZ]

My main question…

I have tried to extract across documents with this code and I do not think it is possible as I get the error 504

=IF(ROWS(B$10:B10)>B$8,"",INDEX('file:...'#$Pix.$B$2:$B$50000,SMALL(IF('file:...'#$Pix.$C$2:$C$50000=A$8,ROW('file:...'#$Pix.$C$2:$C$50000)-ROW('file:...'#$Pix.$C$2)+1),ROWS(B$10:B10))))

If I have made an error or someone can think of a viable fix I would greatly appreciate it.

Or a better way of doing it maybe.

I have stripped them down a lot and I have added some notes on to help you with what I am trying to accomplish.

zTestSmall.ods
zTestGlobal.ods

if it is not clear or you need additional help understanding what I want to do please ask

(*** ---- *** — *** — *** ---- *** — *** — *** ---- *** — *** — *** ---- *** — *** — *** ---- *** — ***)

Not so important…

How do I copy code downwards (or across) as I have dragged a formula down 50,000 rows it took forever and it would be great if there was a fast way. (I know I can double click on the corner of a selected cell, but that doesn’t help if you have not already got a column next to it already filled with data up to your desired stopping point).

(*** ---- *** — *** — *** ---- *** — *** — *** ---- *** — *** — *** ---- *** — *** — *** ---- *** — ***)

This is a free program and the amount of work and effort that has gone into it is amazing, if you use this as much as I do or like what they have done you really should donate as this funds the progress and future of the project.

[mariosv]

Not reference to the other file, when I think it’s no necessary, avoid the issue with ROW() that doesn’t work with external links.

B10:

{=IF(ROWS(B$10:B10)>B$8;"";INDEX('file:///g:/Users/..../Downloads/_Test/zTestGlobal.ods'#$Pix.$B$2:$B$50000;SMALL(IF('file:///g:/Users/..../Downloads/_Test/zTestGlobal.ods'#$Pix.$C$2:$C$50000=A$8;ROW($PicList.$A$2:$A$50000)-ROW(PicList.$A$2)+1);ROWS(B$10:B10))))}

Seems it works fine and not so slow.

In any case I think could be easy linking the whole data, and using a filter or a pivot table to treat them.