Is there a limit to number of successive IF-THEN statements?

[bufossil]

I am listing a number of deliverables, and want the deadlines for those
deliverables to automatically appear in the next column (I have the deadlines
on a tab named "Lists"). Unless I am misunderstanding something, there is a
limit of 7 successive IF-THEN statements you can use. Here is my IF-THEN
formula:

=IF(E4="User Guide",'Lists'!D4,IF(E4="Installation",'Lists'!D5,IF(E4="HW
Upgrade",'Lists'!D3,IF(E4="Maintenance",'Lists'!D6,IF(E4="RAS
Trouble",'Lists'!D3,IF(E4="Online Help",'Lists'!D2,IF(E4="Site
Prep",'Lists'!D7,IF(E4="Inventry Sheet",'Lists'!D10,IF(E4="SW
Upgrade",'Lists'!D9,IF(E4="Release Notes","",IF(E4="Many or
All","",IF(E4="Other or N/A","",""))))))))))))

Excel will not let me use the formula because it has issues with the 8th
IF-THEN statement. My guess is that Excel can only handle 7. Does anyone
know the limit you can use? I have 12 different values that have different
deadlines. Thanks!

[Niek Otten]

There is a limit of 7 nested IFs.
But it would be far better and easier to read if you used the VLOOKUP()
function.
Look in HELP for details, post again in this thread if you still have
problems.

--
Kind regards,

Niek Otten

"bufossil" <[email protected]> wrote in message
news:[email protected]...
>I am listing a number of deliverables, and want the deadlines for those
> deliverables to automatically appear in the next column (I have the
> deadlines
> on a tab named "Lists"). Unless I am misunderstanding something, there is
> a
> limit of 7 successive IF-THEN statements you can use. Here is my IF-THEN
> formula:
>
> =IF(E4="User Guide",'Lists'!D4,IF(E4="Installation",'Lists'!D5,IF(E4="HW
> Upgrade",'Lists'!D3,IF(E4="Maintenance",'Lists'!D6,IF(E4="RAS
> Trouble",'Lists'!D3,IF(E4="Online Help",'Lists'!D2,IF(E4="Site
> Prep",'Lists'!D7,IF(E4="Inventry Sheet",'Lists'!D10,IF(E4="SW
> Upgrade",'Lists'!D9,IF(E4="Release Notes","",IF(E4="Many or
> All","",IF(E4="Other or N/A","",""))))))))))))
>
> Excel will not let me use the formula because it has issues with the 8th
> IF-THEN statement. My guess is that Excel can only handle 7. Does anyone
> know the limit you can use? I have 12 different values that have
> different
> deadlines. Thanks!

[bufossil]

Hi Niek:

I have read the online help, and I guess I don't see how VLOOKUP() can help
me. I have 12 possible values for deliverables in one column, each of which
have their own (different) deadlines. If someone chooses a deliverable from
a drop-down list in column E, then I want the deadline to automatically
appear in column F. I have the deadlines listed in a column on a tab named
Lists. Does that make sense? So if someone selects "User Guide" from the
dropdown list in cell E5, then I want the deadline date [that resides in cell
D4 on the Lists tab] to appear in cell F5.

The online help for VLOOKUP isn't very clear, but it doesn't look like it
can do what I would like for it to do. I will still need Excel to respond to
a succession of 12 possible values in E5, and then fetch the respective value
from a column on the Lists tab. I am misunderstanding something?

Thanks Niek!

"Niek Otten" wrote:

> There is a limit of 7 nested IFs.
> But it would be far better and easier to read if you used the VLOOKUP()
> function.
> Look in HELP for details, post again in this thread if you still have
> problems.
>
> --
> Kind regards,
>
> Niek Otten
>
> "bufossil" <[email protected]> wrote in message
> news:[email protected]...
> >I am listing a number of deliverables, and want the deadlines for those
> > deliverables to automatically appear in the next column (I have the
> > deadlines
> > on a tab named "Lists"). Unless I am misunderstanding something, there is
> > a
> > limit of 7 successive IF-THEN statements you can use. Here is my IF-THEN
> > formula:
> >
> > =IF(E4="User Guide",'Lists'!D4,IF(E4="Installation",'Lists'!D5,IF(E4="HW
> > Upgrade",'Lists'!D3,IF(E4="Maintenance",'Lists'!D6,IF(E4="RAS
> > Trouble",'Lists'!D3,IF(E4="Online Help",'Lists'!D2,IF(E4="Site
> > Prep",'Lists'!D7,IF(E4="Inventry Sheet",'Lists'!D10,IF(E4="SW
> > Upgrade",'Lists'!D9,IF(E4="Release Notes","",IF(E4="Many or
> > All","",IF(E4="Other or N/A","",""))))))))))))
> >
> > Excel will not let me use the formula because it has issues with the 8th
> > IF-THEN statement. My guess is that Excel can only handle 7. Does anyone
> > know the limit you can use? I have 12 different values that have
> > different
> > deadlines. Thanks!
>
>
>

[SteveG]

You could also set up a reference table for your criteria and assign them values based on the data in E4 and use IF and CHOOSE.

In the sheet where you have your criteria (E4) set up a table like:

Range L18:L29
Online Help
HW Upgrade
RAS Trouble
User Guide
Installation
Maintenance
Site Prep
SW Upgrade
Inventry Sheet
Release Notes
Many or All
Other or N/A

Range M18:M29

=IF(E4=L18,1,"")
=IF(E4=L19,2,"")
=IF(E4=L20,3,"")
=IF(E4=L21,4,"")
=IF(E4=L22,5,"")
=IF(E4=L23,6,"")
=IF(E4=L24,7,"")
=IF(E4=L25,8,"")
=IF(E4=L26,9,"")
=IF(E4=L27,10,"")
=IF(E4=L28,11,"")
=IF(E4=L29,12,"")

In cell M30 =SUM(M18:M29)

In the cell F4 then

=IF(M30=0,"",if(M30>0,CHOOSE(M30,Lists!D2,Lists!D3,Lists!D3,Lists!D4,Lists!D5,Lists!D6,Lists!D7,Lists!D9,Lists!D10,"","",""),""))

What the formula CHOOSE does is looks for the value in M30 and then returns a value based on it. So if M30=1,Lists!D2, M30=2, Lists!D3 and so on up sequentially.

It's kind of a long way around it but I am not sure if you can use the VLOOKUP because it doesn't sound like your matching criteria (Online Help etc...) appear in your sheet 'Lists!'. If they do then the VLOOKUP would me a much easier solution.


Cheers,

Steve

[SteveG]

If you need to drag this formula down the column then you need to make the references absolute:

=IF($M$30=0,"",if($M$30>0,CHOOSE($M$30,Lists!$D$2,Lists!$D$3 ,Lists!$D$3,Lists!$D$4,Lists!$D$5,Lists!$D$6,Lists!$D$7,List s!$D$9,Lists!$D$10,"","",""),""))


Cheers,

Steve

[SteveG]

From your last reply, looks like my solution won't work because you are not always referring to the value in E4. Sorry for any confusion! I'll think more on it though.

Regards,

Steve

[Niek Otten]

In A1, put "User Guide. In B1, put "='Lists'!D4"
In A2, put "Installation" In B2, put "='Lists'!D5"
etc

You formula:

=VLOOKUP(E4,A1:B12,2,FALSE)

--
Kind regards,

Niek Otten

"bufossil" <[email protected]> wrote in message
news:[email protected]...
> Hi Niek:
>
> I have read the online help, and I guess I don't see how VLOOKUP() can
> help
> me. I have 12 possible values for deliverables in one column, each of
> which
> have their own (different) deadlines. If someone chooses a deliverable
> from
> a drop-down list in column E, then I want the deadline to automatically
> appear in column F. I have the deadlines listed in a column on a tab
> named
> Lists. Does that make sense? So if someone selects "User Guide" from the
> dropdown list in cell E5, then I want the deadline date [that resides in
> cell
> D4 on the Lists tab] to appear in cell F5.
>
> The online help for VLOOKUP isn't very clear, but it doesn't look like it
> can do what I would like for it to do. I will still need Excel to respond
> to
> a succession of 12 possible values in E5, and then fetch the respective
> value
> from a column on the Lists tab. I am misunderstanding something?
>
> Thanks Niek!
>
> "Niek Otten" wrote:
>
>> There is a limit of 7 nested IFs.
>> But it would be far better and easier to read if you used the VLOOKUP()
>> function.
>> Look in HELP for details, post again in this thread if you still have
>> problems.
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> "bufossil" <[email protected]> wrote in message
>> news:[email protected]...
>> >I am listing a number of deliverables, and want the deadlines for those
>> > deliverables to automatically appear in the next column (I have the
>> > deadlines
>> > on a tab named "Lists"). Unless I am misunderstanding something, there
>> > is
>> > a
>> > limit of 7 successive IF-THEN statements you can use. Here is my
>> > IF-THEN
>> > formula:
>> >
>> > =IF(E4="User
>> > Guide",'Lists'!D4,IF(E4="Installation",'Lists'!D5,IF(E4="HW
>> > Upgrade",'Lists'!D3,IF(E4="Maintenance",'Lists'!D6,IF(E4="RAS
>> > Trouble",'Lists'!D3,IF(E4="Online Help",'Lists'!D2,IF(E4="Site
>> > Prep",'Lists'!D7,IF(E4="Inventry Sheet",'Lists'!D10,IF(E4="SW
>> > Upgrade",'Lists'!D9,IF(E4="Release Notes","",IF(E4="Many or
>> > All","",IF(E4="Other or N/A","",""))))))))))))
>> >
>> > Excel will not let me use the formula because it has issues with the
>> > 8th
>> > IF-THEN statement. My guess is that Excel can only handle 7. Does
>> > anyone
>> > know the limit you can use? I have 12 different values that have
>> > different
>> > deadlines. Thanks!
>>
>>
>>

[[email protected]]

I used to use this work around for long imbedded if statements. In
cell AA1 start the if statement
=IF(E4="User
Guide",'Lists'!D4,IF(E4="Installation",'Lists'!D5,IF(E4="HW
Upgrade",'Lists'!D3,IF(E4="Maintenance",'Lists'!D6,IF(E4="RAS
Trouble",'Lists'!D3,IF(E4="Online Help",'Lists'!D2,AB1)))))) - the last
entry tells it to look in cell AB1 for the rest of teh if statements.
AB1 looks like

=IF(E4="SitePrep",'Lists'!D7,IF(E4="Inventry
Sheet",'Lists'!D10,IF(E4="SW
Upgrade",'Lists'!D9,IF(E4="Release Notes","",IF(E4="Many or
All","",IF(E4="Other or N/A","",""))))))

In the original cell that you had the long formula, put
=if(aa1<>"",aa1,ab1)

[bufossil]

Hi:

I think Niek and Steve have a solution to my problem, but they are a bit
over my head (since I am totally unfamiliar with vloookup). Thanks Niek and
Steve!

However, I do understand everything in Idashburnwest's post, except for the
symbols <>. Can you tell me what the <> is doing?

(Note that I will follow this advice even if I don't understand <>. Thanks!)

"[email protected]" wrote:

> I used to use this work around for long imbedded if statements. In
> cell AA1 start the if statement
> =IF(E4="User
> Guide",'Lists'!D4,IF(E4="Installation",'Lists'!D5,IF(E4="HW
> Upgrade",'Lists'!D3,IF(E4="Maintenance",'Lists'!D6,IF(E4="RAS
> Trouble",'Lists'!D3,IF(E4="Online Help",'Lists'!D2,AB1)))))) - the last
> entry tells it to look in cell AB1 for the rest of teh if statements.
> AB1 looks like
>
> =IF(E4="SitePrep",'Lists'!D7,IF(E4="Inventry
> Sheet",'Lists'!D10,IF(E4="SW
> Upgrade",'Lists'!D9,IF(E4="Release Notes","",IF(E4="Many or
> All","",IF(E4="Other or N/A","",""))))))
>
> In the original cell that you had the long formula, put
> =if(aa1<>"",aa1,ab1)
>
>

[RagDyeR]

Just make the "deliverables" and "deadlines" into a 2 column datalist, and
then access that list using a Vlookup formula like Niek suggested.

Say D1 to D12 on the Sheet "Lists" you have your deadlines.
In C1 to C12 enter your corresponding deliverables.

That gives you a datalist of C1 to D12.

Say your dropdown list is in E1 to E25 on your Main sheet.
Enter this formula in F1:

=IF(E5<>"",IF(ISNA(MATCH(E5,Lists!$C$1:$C$12,0)),"NoMatch",VLOOKUP(E5,Lists!
$C$1:$D$12,2,0)),"")

And copy down.


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"bufossil" <[email protected]> wrote in message
news:[email protected]...
Hi Niek:

I have read the online help, and I guess I don't see how VLOOKUP() can help
me. I have 12 possible values for deliverables in one column, each of which
have their own (different) deadlines. If someone chooses a deliverable from
a drop-down list in column E, then I want the deadline to automatically
appear in column F. I have the deadlines listed in a column on a tab named
Lists. Does that make sense? So if someone selects "User Guide" from the
dropdown list in cell E5, then I want the deadline date [that resides in
cell
D4 on the Lists tab] to appear in cell F5.

The online help for VLOOKUP isn't very clear, but it doesn't look like it
can do what I would like for it to do. I will still need Excel to respond
to
a succession of 12 possible values in E5, and then fetch the respective
value
from a column on the Lists tab. I am misunderstanding something?

Thanks Niek!

"Niek Otten" wrote:

> There is a limit of 7 nested IFs.
> But it would be far better and easier to read if you used the VLOOKUP()
> function.
> Look in HELP for details, post again in this thread if you still have
> problems.
>
> --
> Kind regards,
>
> Niek Otten
>
> "bufossil" <[email protected]> wrote in message
> news:[email protected]...
> >I am listing a number of deliverables, and want the deadlines for those
> > deliverables to automatically appear in the next column (I have the
> > deadlines
> > on a tab named "Lists"). Unless I am misunderstanding something, there
is
> > a
> > limit of 7 successive IF-THEN statements you can use. Here is my
IF-THEN
> > formula:
> >
> > =IF(E4="User Guide",'Lists'!D4,IF(E4="Installation",'Lists'!D5,IF(E4="HW
> > Upgrade",'Lists'!D3,IF(E4="Maintenance",'Lists'!D6,IF(E4="RAS
> > Trouble",'Lists'!D3,IF(E4="Online Help",'Lists'!D2,IF(E4="Site
> > Prep",'Lists'!D7,IF(E4="Inventry Sheet",'Lists'!D10,IF(E4="SW
> > Upgrade",'Lists'!D9,IF(E4="Release Notes","",IF(E4="Many or
> > All","",IF(E4="Other or N/A","",""))))))))))))
> >
> > Excel will not let me use the formula because it has issues with the 8th
> > IF-THEN statement. My guess is that Excel can only handle 7. Does
anyone
> > know the limit you can use? I have 12 different values that have
> > different
> > deadlines. Thanks!
>
>
>

[JE McGimpsey]

a <> b

a is not equal to b

In article <[email protected]>,
"bufossil" <[email protected]> wrote:

> However, I do understand everything in Idashburnwest's post, except for the
> symbols <>. Can you tell me what the <> is doing?

[Ragdyer]

On second thought, I see that I've made the formula unnecessarily long by
inserting an unneeded trap using the Match function.

Since the lookup data in Column E on your main sheet will be inserted by a
dropdown list, there's really no need to check for it's existence in the
datalist.

Also, I referenced the wrong cell (E5) in the formula when I mentioned
starting in F1.

So, try this formula instead:

=IF(E1<>"",VLOOKUP(E1,Lists!$C$1:$D$12,2,0),"")
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"RagDyeR" <[email protected]> wrote in message
news:%[email protected]...
> Just make the "deliverables" and "deadlines" into a 2 column datalist, and
> then access that list using a Vlookup formula like Niek suggested.
>
> Say D1 to D12 on the Sheet "Lists" you have your deadlines.
> In C1 to C12 enter your corresponding deliverables.
>
> That gives you a datalist of C1 to D12.
>
> Say your dropdown list is in E1 to E25 on your Main sheet.
> Enter this formula in F1:
>
> =IF(E5<>"",IF(ISNA(MATCH(E5,Lists!$C$1:$C$12,0)),"NoMatch",VLOOKUP(E5,Lists!
> $C$1:$D$12,2,0)),"")
>
> And copy down.
>
>
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
> "bufossil" <[email protected]> wrote in message
> news:[email protected]...
> Hi Niek:
>
> I have read the online help, and I guess I don't see how VLOOKUP() can
> help
> me. I have 12 possible values for deliverables in one column, each of
> which
> have their own (different) deadlines. If someone chooses a deliverable
> from
> a drop-down list in column E, then I want the deadline to automatically
> appear in column F. I have the deadlines listed in a column on a tab
> named
> Lists. Does that make sense? So if someone selects "User Guide" from the
> dropdown list in cell E5, then I want the deadline date [that resides in
> cell
> D4 on the Lists tab] to appear in cell F5.
>
> The online help for VLOOKUP isn't very clear, but it doesn't look like it
> can do what I would like for it to do. I will still need Excel to respond
> to
> a succession of 12 possible values in E5, and then fetch the respective
> value
> from a column on the Lists tab. I am misunderstanding something?
>
> Thanks Niek!
>
> "Niek Otten" wrote:
>
>> There is a limit of 7 nested IFs.
>> But it would be far better and easier to read if you used the VLOOKUP()
>> function.
>> Look in HELP for details, post again in this thread if you still have
>> problems.
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> "bufossil" <[email protected]> wrote in message
>> news:[email protected]...
>> >I am listing a number of deliverables, and want the deadlines for those
>> > deliverables to automatically appear in the next column (I have the
>> > deadlines
>> > on a tab named "Lists"). Unless I am misunderstanding something, there
> is
>> > a
>> > limit of 7 successive IF-THEN statements you can use. Here is my
> IF-THEN
>> > formula:
>> >
>> > =IF(E4="User
>> > Guide",'Lists'!D4,IF(E4="Installation",'Lists'!D5,IF(E4="HW
>> > Upgrade",'Lists'!D3,IF(E4="Maintenance",'Lists'!D6,IF(E4="RAS
>> > Trouble",'Lists'!D3,IF(E4="Online Help",'Lists'!D2,IF(E4="Site
>> > Prep",'Lists'!D7,IF(E4="Inventry Sheet",'Lists'!D10,IF(E4="SW
>> > Upgrade",'Lists'!D9,IF(E4="Release Notes","",IF(E4="Many or
>> > All","",IF(E4="Other or N/A","",""))))))))))))
>> >
>> > Excel will not let me use the formula because it has issues with the
>> > 8th
>> > IF-THEN statement. My guess is that Excel can only handle 7. Does
> anyone
>> > know the limit you can use? I have 12 different values that have
>> > different
>> > deadlines. Thanks!
>>
>>
>>
>
>