Switch function
 

Hi. I need to use something like a switch function.

I thought it was just "Switch" as it says here (and a couple of other
websites):

http://www.techonthenet.com/excel/formulas/switch.php

but when I try this:

=switch(J4=F2,1,J4=G2,2,3)

I get a name error. I'm using Excel 2003 SP2.

I don't want to use VLookup because I want the value that the function
returns to be from an evaluated formula not from a table and I'm using
references from multiple worksheets.

Switch function
 

Correction
wrote:
Hi. I need to use something like a switch function.

I thought it was just "Switch" as it says here (and a couple of other
websites):

http://www.techonthenet.com/excel/formulas/switch.php

but when I try this:

=switch(J4=F2,1,J4=G2,2,3)
=switch(J4=F2,1,J4=G2,2,J4=H2,3)
actually gives the name error (although the above will give a name
error as well)


I get a name error. I'm using Excel 2003 SP2.

I don't want to use VLookup because I want the value that the function
returns to be from an evaluated formula not from a table and I'm using
references from multiple worksheets.

Switch function
 

Maybe this is what you want

=IF(J4=F2,1,IF(J4=G2,2,3))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
Hi. I need to use something like a switch function.

I thought it was just "Switch" as it says here (and a couple of other
websites):

http://www.techonthenet.com/excel/formulas/switch.php

but when I try this:

=switch(J4=F2,1,J4=G2,2,3)

I get a name error. I'm using Excel 2003 SP2.

I don't want to use VLookup because I want the value that the function
returns to be from an evaluated formula not from a table and I'm using
references from multiple worksheets.

Switch function
 

Bob Phillips wrote:
Maybe this is what you want

=IF(J4=F2,1,IF(J4=G2,2,3))


I tried that, but it really sucks beyond two cases. Did you know that
you can only nest formulas 7 (or is it 8) levels deep in excel making
nested ifs even worse.

Switch function
 

Did you miss this line in the article:

"The Switch function can only be used in VBA code."

Biff

wrote in message
ups.com...
Hi. I need to use something like a switch function.

I thought it was just "Switch" as it says here (and a couple of other
websites):

http://www.techonthenet.com/excel/formulas/switch.php

but when I try this:

=switch(J4=F2,1,J4=G2,2,3)

I get a name error. I'm using Excel 2003 SP2.

I don't want to use VLookup because I want the value that the function
returns to be from an evaluated formula not from a table and I'm using
references from multiple worksheets.

Switch function
 

I guess that I did.

Listen up Microsoft people: this function (or a similarly capable one)
should be available in worksheet functions.

I've had several occasions where I needed it.

Is there a way that I can write a VBA function and make it available as
a worksheet function?

if( ,,if(,,if(,,if(,,... really is awful.

Switch function
 

Of course I do.

=IF(J4=F2,1,"")&IF(J4=G2,2,"")&...&IF(J4=final_cel l,final_num,"")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...

Bob Phillips wrote:
Maybe this is what you want

=IF(J4=F2,1,IF(J4=G2,2,3))


I tried that, but it really sucks beyond two cases. Did you know that
you can only nest formulas 7 (or is it 8) levels deep in excel making
nested ifs even worse.

Switch function
 

if( ,,if(,,if(,,if(,,... really is awful.

Sometimes! But there are several ways to avoid if( ,,if(,,if(,,if(,,...

If you can explain in detail what you're trying to do we'll have a better
chance of coming up with an alternative.

Biff

wrote in message
ups.com...
I guess that I did.

Listen up Microsoft people: this function (or a similarly capable one)
should be available in worksheet functions.

I've had several occasions where I needed it.

Is there a way that I can write a VBA function and make it available as
a worksheet function?

if( ,,if(,,if(,,if(,,... really is awful.

Switch function
 

I have a worksheet--call it sheet one--which is caclculating a certain
attribute for an entity--one entity per line.

Another worksheet--call it sheet 2--holds the parameters to that
function again one entity per line. One of the parameter is the entity
type, type 1, type 2, type 3, etc.

The entity type determines the form of the function for example:
for entities of type 1, the function might be '=param1*param2 + param3'
whereas for entities of type 2, the function might be '=param1*param2'
whereas for entities of type 3 the function might be '=param3+param2',
etc.

Biff wrote:
if( ,,if(,,if(,,if(,,... really is awful.

Sometimes! But there are several ways to avoid if( ,,if(,,if(,,if(,,...

If you can explain in detail what you're trying to do we'll have a better
chance of coming up with an alternative.

Biff

wrote in message
ups.com...
I guess that I did.

Listen up Microsoft people: this function (or a similarly capable one)
should be available in worksheet functions.

I've had several occasions where I needed it.

Is there a way that I can write a VBA function and make it available as
a worksheet function?

if( ,,if(,,if(,,if(,,... really is awful.

Switch function
 

=CHOOSE(MATCH(TYPE,{Type1","Type2","Type3},0),para m1*param2 +
param3,param1*param2,param3+param2)

As you can see that MIGHT not appear to be any better than
IF(...IF(...IF( but it is! You can have up to 29 conditions versus 7 nested
functions (8 counting the top level).

Biff

wrote in message
oups.com...
I have a worksheet--call it sheet one--which is caclculating a certain
attribute for an entity--one entity per line.

Another worksheet--call it sheet 2--holds the parameters to that
function again one entity per line. One of the parameter is the entity
type, type 1, type 2, type 3, etc.

The entity type determines the form of the function for example:
for entities of type 1, the function might be '=param1*param2 + param3'
whereas for entities of type 2, the function might be '=param1*param2'
whereas for entities of type 3 the function might be '=param3+param2',
etc.

Biff wrote:
if( ,,if(,,if(,,if(,,... really is awful.

Sometimes! But there are several ways to avoid if( ,,if(,,if(,,if(,,...

If you can explain in detail what you're trying to do we'll have a better
chance of coming up with an alternative.

Biff

wrote in message
ups.com...
I guess that I did.

Listen up Microsoft people: this function (or a similarly capable one)
should be available in worksheet functions.

I've had several occasions where I needed it.

Is there a way that I can write a VBA function and make it available as
a worksheet function?

if( ,,if(,,if(,,if(,,... really is awful.