that damned IF function
 

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

that damned IF function
 

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

that damned IF function
 

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

that damned IF function
 

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

that damned IF function
 

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

that damned IF function
 

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

that damned IF function
 

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

that damned IF function
 

"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

that damned IF function
 

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