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.

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.

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.

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.

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.

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.

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.

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.

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.

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