Automatically fill in links name

[siwitje]

Hello,

I have an Excel file which extracts data from other excel files (the other excel files are named 1.xls, 2.xls, 3.xls, etc.). So the formula in the cell is for example: ='C:\[1.xls]Sheet1'!A1
Is there a way to let Excel fill in the next excel file address for the next cell? So that i would get this:

='C:\[1.xls]Sheet1'!A1
='C:\[2.xls]Sheet1'!A1
='C:\[3.xls]Sheet1'!A1
='C:\[4.xls]Sheet1'!A1
etc..

Because i have about 500 excel files, and otherwise i have to change the file number manually...

[jceg316]

if you have the first 4 written out, what happens if you select them all and click and drag down? Excel should auto fill the numbers in order.

If not, in column A type "=C:\[", in column B type "1" and in column C type ".xls]Sheet1'!A1". In column D type the formula "=concatenate(A1,B1,C1).

Select those cells and drag down to cell 500 and coumn D should contain the data you want. To "flatten" it select the column, press CTRL C then CTRL V and paste as values.

[siwitje]

If i select all 4 cells and drag it down I get:
='C:\[1.xls]Sheet1'!A1
='C:\[2.xls]Sheet1'!A1
='C:\[3.xls]Sheet1'!A1
='C:\[4.xls]Sheet1'!A1
='C:\[1.xls]Sheet1'!A1
='C:\[2.xls]Sheet1'!A1
='C:\[3.xls]Sheet1'!A1
='C:\[4.xls]Sheet1'!A1
and so on

And your second solution, excel already gives an error when type in column A "=C:\[" because thats not a valid formula.