Automatically changing link. How?

[sgkelly]

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.)

 

[bpeltzer]

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]

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

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

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.)