Auto Increment Externally linked Filename??? Please Help!!

[xlyfe]

Hello,

I've been doing extensive research on this topic, and I'm coming up short on an answer. Hopefully somebody here can help me out.

What I'm trying to create is a log file which I would like to have auto populate certain values from cells within many other excel files, which all happen to be in the same folder location on our server.

One of the columns of data that I'm trying to pull into this log file is the sum of a cost from one/all of the excel files located in a different folder.

I've got the first few cells to transfer the data I'm looking for by using the formual... =SUM('\\twsbs01\common$\preferred fire reconstruction\project management\po\po excel\[2013-0002.xls]Purchase Order'!H37)

What I'm trying to do though, is drag the cell with that formula, down 1,000 or more cells, and have the file name [2013-0002.xls], auto increment itself while I copy it. For example, I would like for it to automatically change to [2013-0003.xls], [2013-0004.xls], [2013-0005.xls].... all the way to [2013-1000.xls], and even further as time goes on. Nothing else in the formula needs to auto increment, only the filename. Is this possible? I just don't want to have to copy this thing down a million times and then have to go in and manually edit the file name myself if I don't have to.

Hopefully this makes sense enough for somebody to help me out.

Thank You!!

[MarkinTX]

You can use the Indirect Method

See the attached spreadsheet.
I used Text() function in B to build the 0001 and 0002 portions you need to add up.
Column C builds the file location
Column A uses indirect to link to your document.





<------Please click the star if this helps you.

[MarkinTX]

Is this what you were looking for, did it work for you?

[xlyfe]

It got me closer to the answer that I was searching for Markin. Thank you for taking the time to respond to my inquiry.

See below for what I came up with that is working great for our purposes. Just takes a little bit longer than I thought to calculate each time.

=INDIRECT.EXT("'\\twsbs01\common$\preferred fire reconstruction\project management\po\po excel\["&$A3&"]Purchase Order'!C15:C36")