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

Hi!

I really hope someone can help me with this issue. I have an 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 expand 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

If you want to extend this part in your formula $A$2:$A$470 to $A$2:$A$600, you can do so by using Find and Replace. The keyboard shortcut is Ctrl+H. in the Find field input $A$2:$A$470 and in the Replace field $A$2:$A$600. This will replace everything, even if in a formula.

Hi Sinon05,

Thanks for the quick reply. I just tried what you suggested and it doesn't extend the actual array. For example, the array on my sheet (file A) is active between rows 450 and 750 (300 rows) across many columns. So even if I use Find&Replace to fetch the data from rows 2 to 1000 of my source file (file B), it won't be on my sheet past row 750 (in file A).
Would there be a way to lengthen the actual arrays in file A?

Thanks again

Howard

If what you want to do is apply the array formula to other rows, simply copy any cells with the formula and paste in the additional rows. Alternatively, you can use flash fill in the DATA tab

Hi Sinon05,

Thanks for the help. That's exactly what I'd like to do. I might not be doing it correctly but when I copy/paste the formula into the additional rows following my array formula, it displays the values from the cells at the beginning of my source file (e.g. Cell A471 in File A should display data from A471 of the source file (File B) but is instead showing A2 from the source file again, as though the array formula is restarting). I also tried to drag & drop the previous rows with the arrays into the new rows and it gave me the same result.

I know manually going into each array would probably work - edit each array individually and ctrl+shift+enter for each until row 600. However, I'm hoping to avoid having to do this as there are quite a few columns...

Thanks again

Howard

Try replacing $A$2 with A2. the dollar signs lock the cell in excel; for instance if you have $A2 in a formula, no matter where you apply the formula, it will always refer to column A. If you have A$2 in a formula, no matter where you apply the formula, it will always refer to row 2. So $A$2 always refers to cell A2 no matter where you apply it. Hope that helps