Increment File Name

[TCPhoenix]

I want am writing some excel formulas that reference other files. Is there a way to increment the file name so I do not have to rewrite the formula for every case. The example i am working on deals with files named WEEK1, and every week a new file will be created WEEK2, WEEK3 etc.

Any thoughts?

 

[joerd]

Take a look at the INDIRECT function. So if your first week formula is something like [tt]=WEEK1!$A$1[/tt] you can change that to [tt]=INDIRECT("WEEK"&D1&"!$A$1")[/tt] where the value in cell D1 (on the same worksheet as the formula) is the week number. If you change the value in D1, the week number reference will change. Modify as needed ;-)

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[yogia]

Hi TCPheonix:

I am not sure if this is what you are looking for ... anyway if your file is named according to current week number, then you can get the current week using the formula ...

=WEEKNUM(TODAY())

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com