variable file reference

Hi,

I have a file that needs to be created on a monthly basis, which contains the same information. So, there are a bunch of these excel files in the exact same format, only with different data. I then have another file ("File A") that references these files (ex: "File B", "File C", "File D", etc.) and pulls out the values and manipulates them in different ways, but I was trying to figure out a good way to streamline the process so that all the links in File A don't have to be updated all the time. The problem with using the Edit-->Links function, is that if I edit a link, it will change the data in File A. I simply want to add to the spreadsheet every month (so it basically keeps a monthly record) with the new File E that is created. I was thinking about creating a cell that allows the user to input the current name of the file being referenced, and then using that cell as the source for the name of the file to begin the cell reference code. That, however, doesn't seem to work, because I guess I can't add a specific cell reference (ex: C6) from File B, to the end of the file name, when the file name is itself a cell reference in File A. I hope it isn't too confusing...I tried to make it as understandable as possible. Thanks!

Thanks! I have another question...say I wanted to return the cell location through some sort of vlookup kind of function instead of the $B$1 part of the formula you gave. Is that possible?

say for example that A1 had the word "US" in it, and B1 had "2.5" in it. If i did a vlookup:

=vlookup("US",A1:C5,2,)

then it would return 2.5, but what if I wanted it to return "B1" instead. Is that possible?

VLOOKUP and HLOOKUP are the obvious lookup options, however MATCH is often more versatile.

for your problem link MATCH with the ADDRESS function as follows:

=ADDRESS(MATCH("US",A1:A5,0),2)

this will return the string "$B$1" (for your example).

Mark.