I have used a combination of HYPERLINK and IF to open a particular file
depending on the month serial number of the computer. The problem is I need
to able to set the formula for a twelve month period and IF only allows seven
functions. Any and all suggestions gratefully accepted

Hi

You could use
=CHOOSE(MONTH(your_date),choice1,choice2,choice3,. ....choice12)

--
Regards

Roger Govier


"sako 338" <sako wrote in message
...
I have used a combination of HYPERLINK and IF to open a particular file
depending on the month serial number of the computer. The problem is I
need
to able to set the formula for a twelve month period and IF only
allows seven
functions. Any and all suggestions gratefully accepted

Use CHOOSE:

=CHOOSE(A1,HYPERLINK("http://www.cnn.com","new"),HYPERLINK("msnbc.com","new2"))


for A1 equals one or two. You can expand.
--
Gary's Student


"sako 338" wrote:

I have used a combination of HYPERLINK and IF to open a particular file
depending on the month serial number of the computer. The problem is I need
to able to set the formula for a twelve month period and IF only allows seven
functions. Any and all suggestions gratefully accepted

I couldn't get the suggestions to work (dunce) what i have at the moment is
=IF(L4=1,HYPERLINK("c:\K9 Stats\Cowie Stats\2007\January\Running Tally Jan
07.xls"),IF(L4=2.........(each new (IF)goes up by one month.

I need to extend this to cover twelve months (therein lies the nesting
problem with the seven level limit)

HELP

Sako 338

"Roger Govier" wrote:

Hi

You could use
=CHOOSE(MONTH(your_date),choice1,choice2,choice3,. ....choice12)

--
Regards

Roger Govier


"sako 338" <sako wrote in message
...
I have used a combination of HYPERLINK and IF to open a particular file
depending on the month serial number of the computer. The problem is I
need
to able to set the formula for a twelve month period and IF only
allows seven
functions. Any and all suggestions gratefully accepted

Don't use IF(), but use the CHOOSE() function, as Roger suggested.
As you've already got a variable set to the month serial number you don't
need the MONTH() function which Roger mentioned, but can merely use the
formula
=CHOOSE(L4,choice1,choice2,choice3,.....choice12)
--
David Biddulph

"sako 338" wrote in message
...
I couldn't get the suggestions to work (dunce) what i have at the moment is
=IF(L4=1,HYPERLINK("c:\K9 Stats\Cowie Stats\2007\January\Running Tally Jan
07.xls"),IF(L4=2.........(each new (IF)goes up by one month.

I need to extend this to cover twelve months (therein lies the nesting
problem with the seven level limit)

"Roger Govier" wrote:

Hi

You could use
=CHOOSE(MONTH(your_date),choice1,choice2,choice3,. ....choice12)

--
Regards

Roger Govier

"sako 338" <sako wrote in message
...
I have used a combination of HYPERLINK and IF to open a particular file
depending on the month serial number of the computer. The problem is I
need
to able to set the formula for a twelve month period and IF only
allows seven
functions. Any and all suggestions gratefully accepted

I thought I had a handle on excel but apparantly not. i can't get choose to
perform the same way my IF function does. the file that is opened depending
on the month, is a new excel file.the IF/HYPERLINK formula that i posted
works a treat but not for the year. some more guidance would most helpfull

sako 338

"David Biddulph" wrote:

Don't use IF(), but use the CHOOSE() function, as Roger suggested.
As you've already got a variable set to the month serial number you don't
need the MONTH() function which Roger mentioned, but can merely use the
formula
=CHOOSE(L4,choice1,choice2,choice3,.....choice12)
--
David Biddulph

"sako 338" wrote in message
...
I couldn't get the suggestions to work (dunce) what i have at the moment is
=IF(L4=1,HYPERLINK("c:\K9 Stats\Cowie Stats\2007\January\Running Tally Jan
07.xls"),IF(L4=2.........(each new (IF)goes up by one month.

I need to extend this to cover twelve months (therein lies the nesting
problem with the seven level limit)

"Roger Govier" wrote:

Hi

You could use
=CHOOSE(MONTH(your_date),choice1,choice2,choice3,. ....choice12)

--
Regards

Roger Govier

"sako 338" <sako wrote in message
...
I have used a combination of HYPERLINK and IF to open a particular file
depending on the month serial number of the computer. The problem is I
need
to able to set the formula for a twelve month period and IF only
allows seven
functions. Any and all suggestions gratefully accepted

Hi

Well lets try a different approach without IF or Choose.
Using any set of available empty cells - I used M1 to M5
In M1 enter "c:\K9 Stats\Cowie Stats\2007\
in M2 enter =TEXT(DATE(7,L4,1),"mmmm")
In M3 enter \Running Tally
in M4 enter =LEFT(M2,3)
in M5 enter 07.xls" That's a space before the 07.xls in the cell)

Your resulting hyperlink formula would then be
=HYPERLINK(M1&M2&M3&M4&M5)

and with a 1 in cell L4 would give the following hyperlink
c:\K9 Stats\Cowie Stats\2007\January\Running Tally Jan 07.xls

and with a 12 in L4 the hyperlink would change to
c:\K9 Stats\Cowie Stats\2007\December\Running Tally Dec 07.xls

You could adapt the method further and have the Year 2007 and O7.xls
part of the file name altered automatically if required.

--
Regards

Roger Govier


"sako 338" wrote in message
...
I thought I had a handle on excel but apparantly not. i can't get
choose to
perform the same way my IF function does. the file that is opened
depending
on the month, is a new excel file.the IF/HYPERLINK formula that i
posted
works a treat but not for the year. some more guidance would most
helpfull

sako 338

"David Biddulph" wrote:

Don't use IF(), but use the CHOOSE() function, as Roger suggested.
As you've already got a variable set to the month serial number you
don't
need the MONTH() function which Roger mentioned, but can merely use
the
formula
=CHOOSE(L4,choice1,choice2,choice3,.....choice12)
--
David Biddulph

"sako 338" wrote in message
...
I couldn't get the suggestions to work (dunce) what i have at the
moment is
=IF(L4=1,HYPERLINK("c:\K9 Stats\Cowie Stats\2007\January\Running
Tally Jan
07.xls"),IF(L4=2.........(each new (IF)goes up by one month.

I need to extend this to cover twelve months (therein lies the
nesting
problem with the seven level limit)

"Roger Govier" wrote:

Hi

You could use
=CHOOSE(MONTH(your_date),choice1,choice2,choice3,. ....choice12)

--
Regards

Roger Govier

"sako 338" <sako wrote in message
...
I have used a combination of HYPERLINK and IF to open a
particular file
depending on the month serial number of the computer. The
problem is I
need
to able to set the formula for a twelve month period and IF only
allows seven
functions. Any and all suggestions gratefully accepted

"Roger Govier" wrote:

Hi

Well lets try a different approach without IF or Choose.
Using any set of available empty cells - I used M1 to M5
In M1 enter "c:\K9 Stats\Cowie Stats\2007\
in M2 enter =TEXT(DATE(7,L4,1),"mmmm")
In M3 enter \Running Tally
in M4 enter =LEFT(M2,3)
in M5 enter 07.xls" That's a space before the 07.xls in the cell)

Your resulting hyperlink formula would then be
=HYPERLINK(M1&M2&M3&M4&M5)

and with a 1 in cell L4 would give the following hyperlink
c:\K9 Stats\Cowie Stats\2007\January\Running Tally Jan 07.xls

and with a 12 in L4 the hyperlink would change to
c:\K9 Stats\Cowie Stats\2007\December\Running Tally Dec 07.xls

You could adapt the method further and have the Year 2007 and O7.xls
part of the file name altered automatically if required.

--
Regards

Roger Govier


Thanks a bunch

Thats was awesome,works a treat.

Thanks

sako 338

"sako 338" wrote in message
...
I thought I had a handle on excel but apparantly not. i can't get
choose to
perform the same way my IF function does. the file that is opened
depending
on the month, is a new excel file.the IF/HYPERLINK formula that i
posted
works a treat but not for the year. some more guidance would most
helpfull

sako 338

"David Biddulph" wrote:

Don't use IF(), but use the CHOOSE() function, as Roger suggested.
As you've already got a variable set to the month serial number you
don't
need the MONTH() function which Roger mentioned, but can merely use
the
formula
=CHOOSE(L4,choice1,choice2,choice3,.....choice12)
--
David Biddulph

"sako 338" wrote in message
...
I couldn't get the suggestions to work (dunce) what i have at the
moment is
=IF(L4=1,HYPERLINK("c:\K9 Stats\Cowie Stats\2007\January\Running
Tally Jan
07.xls"),IF(L4=2.........(each new (IF)goes up by one month.

I need to extend this to cover twelve months (therein lies the
nesting
problem with the seven level limit)

"Roger Govier" wrote:

Hi

You could use
=CHOOSE(MONTH(your_date),choice1,choice2,choice3,. ....choice12)

--
Regards

Roger Govier

"sako 338" <sako wrote in message
...
I have used a combination of HYPERLINK and IF to open a
particular file
depending on the month serial number of the computer. The
problem is I
need
to able to set the formula for a twelve month period and IF only
allows seven
functions. Any and all suggestions gratefully accepted

Hi

You're very welcome.
Thanks for the feedback letting us know it worked.

--
Regards

Roger Govier


"sako 338" wrote in message
...


"Roger Govier" wrote:

Hi

Well lets try a different approach without IF or Choose.
Using any set of available empty cells - I used M1 to M5
In M1 enter "c:\K9 Stats\Cowie Stats\2007\
in M2 enter =TEXT(DATE(7,L4,1),"mmmm")
In M3 enter \Running Tally
in M4 enter =LEFT(M2,3)
in M5 enter 07.xls" That's a space before the 07.xls in the
cell)

Your resulting hyperlink formula would then be
=HYPERLINK(M1&M2&M3&M4&M5)

and with a 1 in cell L4 would give the following hyperlink
c:\K9 Stats\Cowie Stats\2007\January\Running Tally Jan 07.xls

and with a 12 in L4 the hyperlink would change to
c:\K9 Stats\Cowie Stats\2007\December\Running Tally Dec 07.xls

You could adapt the method further and have the Year 2007 and O7.xls
part of the file name altered automatically if required.

--
Regards

Roger Govier


Thanks a bunch

Thats was awesome,works a treat.

Thanks

sako 338