Can't extend array linked to another excel file!?

[hjones89]

Hi!


I really hope someone can help me with this issue. I have a spreadsheet with MANY columns containing array formulas to display values from columns in another excel file. For example: {=if('file_path\file.xls'$A$2:$A$500="","",'file_path\file.xls'$A$2:$A$470}.


Basically, when it would get close to the end of the array, I would insert rows in the source document (to which the arrays are linked), and it would extend the array formula, so instead of it going until row 500, I insert 100 rows and the array formula would work until row 600, and so forth. However, I'm trying to do it now and it's simply not working.


Am I inserting the rows incorrectly? Or is there another way I could extend the array across many different columns without editing each one individually?


THANK YOU SO MUCH!


Howard

 

[Rijnsent]

Hi Howard,
I trick I often use is "search & replace" (CTRL+H), then replace e.g. $A$470 by $A$500, that should work.
Cheers,
Koen

 

[hjones89]

Hi Koen,
Thanks for the reply! That helps me fetch more data from the source document but unfortunately doesn't extend my actual array. For instance, the array on my sheet (file A) is active between rows 450 and 750 (300 rows) across many columns, and displays the values from the source file (file B). However, the actual array ends at row 750, and I am hoping to extend the actual array formula to more rows beyond row 750 (across several columns).
I tried copy/pasting the array formula into the following rows though that seems to restart the array and display data from the top of my source file.

I hope I could do extend the arrays without editing each one individually.

Thanks again

Howard

 

[Rijnsent]

Hi Howard,
would you be able to post the files here (a dropbox/google drive/etc. link)? Or otherwise a mockup version with dummy data? I'm trying to reproduce your issue, but fail to do so.
Cheers,
Koen