MOre than 8 argurement
 

Hi all,
I have done if function with 8 argurement/condition with no problem.Is
there
anyway for me to have more than 8 condition using if function?

here is the example,
=IF($S$1=1,B3,IF($S$1=2,C3,IF($S$1=3,D3,IF($S$1=4, E3,IF($S$1=5,F3,IF($S$1=6,G3,IF($S$1=7,H3,IF($S$1= 8,I3,0))))))))

cheers

Wan


--
wan
------------------------------------------------------------------------
wan's Profile: http://www.excelforum.com/member.php...o&userid=37523
View this thread: http://www.excelforum.com/showthread...hreadid=571675

MOre than 8 argurement
 

No is the short answer, you can combine 2 cells to overcome the problem
but what are you wanting to do anyway

It seems that a lookup or a simple formula could work instead and you
would not need the multiple ifs. You may need to put the formula inside
an if statement to stop it going out of range or if whole numbers do not
appear

but basically you are saying

=OFFSET(A3,0,S1)

or

=if(and(s1=1,s1<=8,trunc(s1)=s1),OFFSET(A3,0,S1), 0)

excludes none whole numbers and allows numbers between 1 and 8

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=571675

MOre than 8 argurement
 

=IF($S$1=1,B3,IF($S$1=2,C3,IF($S$1=3,D3,IF($S$1=4, E3,
IF($S$1=5,F3,IF($S$1=6,G3,IF($S$1=7,H3,IF($S$1=8,I 3,0))))))))

Perhaps a better, simpler alternative to achieve the above ..
(wo being limited by the max nested IFs)
is to use something like this in say, T1:
=IF(S1="","",INDEX(B3:IV3,,S1))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"wan" wrote:

Hi all,
I have done if function with 8 argurement/condition with no problem.Is
there
anyway for me to have more than 8 condition using if function?

here is the example,
=IF($S$1=1,B3,IF($S$1=2,C3,IF($S$1=3,D3,IF($S$1=4, E3,IF($S$1=5,F3,IF($S$1=6,G3,IF($S$1=7,H3,IF($S$1= 8,I3,0))))))))

cheers

Wan


--
wan
------------------------------------------------------------------------
wan's Profile: http://www.excelforum.com/member.php...o&userid=37523
View this thread: http://www.excelforum.com/showthread...hreadid=571675

MOre than 8 argurement
 

Actually you can. I am not advocating this approach in this particular
instance, but just to show how

=IF($S$1=1,B3,"")&IF($S$1=2,C3,"")&IF($S$1=3,D3,"" )&IF($S$1=4,E3,"")&IF($S$1
=5,F3,"")&IF($S$1=6,G3,"")&IF($S$1=7,H3,"")&IF($S$ 1=8,I3,"")&IF($S$1=9,J3,""
)&IF($S$1=10,K3,"")&IF($S$1=11,L3,"")

--
HTH

Bob Phillips

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

"Dav" wrote in message
...

No is the short answer, you can combine 2 cells to overcome the problem
but what are you wanting to do anyway

It seems that a lookup or a simple formula could work instead and you
would not need the multiple ifs. You may need to put the formula inside
an if statement to stop it going out of range or if whole numbers do not
appear

but basically you are saying

=OFFSET(A3,0,S1)

or

=if(and(s1=1,s1<=8,trunc(s1)=s1),OFFSET(A3,0,S1), 0)

excludes none whole numbers and allows numbers between 1 and 8

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile:
http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=571675

MOre than 8 argurement
 

Thanks for quick repondes,

Cheers,
Wan


--
wan
------------------------------------------------------------------------
wan's Profile: http://www.excelforum.com/member.php...o&userid=37523
View this thread: http://www.excelforum.com/showthread...hreadid=571675

MOre than 8 argurement
 

Dav Wrote:
No is the short answer, you can combine 2 cells to overcome the problem
but what are you wanting to do anyway

It seems that a lookup or a simple formula could work instead and you
would not need the multiple ifs. You may need to put the formula inside
an if statement to stop it going out of range or if whole numbers do not
appear

but basically you are saying

=OFFSET(A3,0,S1)

or

=if(and(s1=1,s1<=8,trunc(s1)=s1),OFFSET(A3,0,S1), 0)

excludes none whole numbers and allows numbers between 1 and 8

Regards

Dav

Thanks Dav,
I decided to use offset and it works well.Basically I got drop down
menu format which link to cell s1.

Cheers


--
wan
------------------------------------------------------------------------
wan's Profile: http://www.excelforum.com/member.php...o&userid=37523
View this thread: http://www.excelforum.com/showthread...hreadid=571675