Excel Hyperlink Question

[HugeFish4]

I am using Excel Hyperlink to have "Hot Links" in one excel file that link to several other excel files. However, I would like the links to be updated once a month to reflect the new month's file(s) with out having to to update each hyperlink separately. For example, if I have the following file in the following drive:

H:\Sep\12345.xls

and I do an edit replace "Sep" with "Oct", the link will appear as follows:

H:\Oct\12345.xls

However, the Hyperlink is really still pointing to the September File!!!! Is this unavoidable?????

Help!

 

[HugeFish4]

Bump

 

[Scottcmu]

Here is one potential way to do it:
Let's say the new sheet is created on the first of every month for the previous month. So, by that I mean if today is December, you'll want to reference Nov in that file you're talking about, and continue to reference Nov until Jan 1 at which time you want to reference Dec. If this is not the case, you'll need to adjust the formulas below.

Create a new sheet in your workbook, and input this formula into A1
=INDEX(A2:A13,month(today())-1,1)

Create this table in A2:A13
A
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

Now, here's the part that I don't know if it will work: In the hyperlink, under "Link to File or URL:" paste this formula (or some variant of it):
=indirect("H:\"&A1&"\12345.xls")

If this doesn't work, perhaps at least it will lead you in the right direction.

Scott