Automatically changing link. How?

I use a template to make a report every month in Excel. The naming convention
I use is “2006MarSwimware” for the March 2006 Swimware report we send to the
OhioEPA. I do not make a year’s worth of blank reports in advance for good
reasons I don’t need to get into here. Now, what I’d like to be able to do is
put a link to a particular cell in the previous month’s report on the
template that would automatically rename itself. I tried combing text and a
link to the month-1 on the current report but instead of getting
“2006MarSwimware” I get “'[200638807Swimware.xls]DATA - Cover'!$C$3. Am I
attempting the impossible? (The link that produced this is
="'[2006"&(K2)&"Swimware.xls]DATA - Cover'!$C$3" Where K2 is a link to a cell
with the formula =C4-1 that returns “Mar” and C4 is the date 4-01-2006.)

It looks as though K2 may read "Mar", but that's it's really just a
particular format of the date 3/31. If K2 isn't used for anything else, you
might set K2 to be =text(c4-1,"Mmm"). Alternately, perform the conversion
within the formula that calculates the filename:
="'[2006"&text(K2,"Mmm")&"Swimware.xls]DATA - Cover'!$C$3"
--Bruce

"sgkelly" wrote:

> I use a template to make a report every month in Excel. The naming convention
> I use is “2006MarSwimware” for the March 2006 Swimware report we send to the
> OhioEPA. I do not make a year’s worth of blank reports in advance for good
> reasons I don’t need to get into here. Now, what I’d like to be able to do is
> put a link to a particular cell in the previous month’s report on the
> template that would automatically rename itself. I tried combing text and a
> link to the month-1 on the current report but instead of getting
> “2006MarSwimware” I get “'[200638807Swimware.xls]DATA - Cover'!$C$3. Am I
> attempting the impossible? (The link that produced this is
> ="'[2006"&(K2)&"Swimware.xls]DATA - Cover'!$C$3" Where K2 is a link to a cell
> with the formula =C4-1 that returns “Mar” and C4 is the date 4-01-2006.)

This does put "Mar" in link however it no longer functions as a link.

"bpeltzer" wrote:

> It looks as though K2 may read "Mar", but that's it's really just a
> particular format of the date 3/31. If K2 isn't used for anything else, you
> might set K2 to be =text(c4-1,"Mmm"). Alternately, perform the conversion
> within the formula that calculates the filename:
> ="'[2006"&text(K2,"Mmm")&"Swimware.xls]DATA - Cover'!$C$3"
> --Bruce
>
> "sgkelly" wrote:
>
> > I use a template to make a report every month in Excel. The naming convention
> > I use is “2006MarSwimware” for the March 2006 Swimware report we send to the
> > OhioEPA. I do not make a year’s worth of blank reports in advance for good
> > reasons I don’t need to get into here. Now, what I’d like to be able to do is
> > put a link to a particular cell in the previous month’s report on the
> > template that would automatically rename itself. I tried combing text and a
> > link to the month-1 on the current report but instead of getting
> > “2006MarSwimware” I get “'[200638807Swimware.xls]DATA - Cover'!$C$3. Am I
> > attempting the impossible? (The link that produced this is
> > ="'[2006"&(K2)&"Swimware.xls]DATA - Cover'!$C$3" Where K2 is a link to a cell
> > with the formula =C4-1 that returns “Mar” and C4 is the date 4-01-2006.)