formula for showing cell data from multiple external workbooks

Hi guys i have this piece of code below which i have used in a the attached spreadsheet, now this links to an external spreadsheet so i can view that information in the compendium. I have this working perfectly fine for the three documents in the demo (even though i have not copied the code for the rest of the row).

Sample of code:

Please Login or Register  to view this content.

What i want to do is be able to copy this formula down like you can do with 3 numbered cells of 1,2,3 to apply a recuring formula in every row below this so this part of the formula:

EXT0000002

is changed to

EXT0000003

on the next row and then

EXT0000004

on the row below that, so i dont need to manually create the code on each line.

Thanks

That requires an INDIRECT() formula, but you can't refer to workbooks INDIRECTly if they're closed, at least not with native Excel Functions.

If you're up for installing an ADD-IN, the MOREFUNC add-in includes INDIRECT.EXT() that allows closed workbook referencing. Read all about it here:
http://xcell05.free.fr/morefunc/english/

Download and install it from here:
http://download.cnet.com/Morefunc/30...-10423159.html

Go into TOOLS > ADDINS and activate MoreFunc.

Then a formula like this should work:
=INDIRECT("'V:\Archive\Master File Backup\Master - Templates (xls doc)\External Site Form\Test\[EXT000000" & ROWS($A$1:$A2) & ".xls]Data Sort'!$C$5")


The only problem I foresee is the the ROWS($A$1:$A2) formula returns a 2 to start, and not an 02. You may have to adjust the formula when you get to the one for the first 2-digit answer of 10, then it would be:

=INDIRECT("'V:\Archive\Master File Backup\Master - Templates (xls doc)\External Site Form\Test\[EXT00000" & ROWS($A$1:$A10) & ".xls]Data Sort'!$C$5")

(took one of the zeros in the EXT00000 section)