Problem with nested logical formula.

Why can't I get the formula below to work? There are 6 sheets in my
worksheet, each of which calculates a payment with different options. The
formula is located on a 7th sheet that is in the same workbook. The formula
should pull the correct payment from the correct sheet based on the options
selected by a customer who will put an "x" in one, or both of two boxes and
choosing either "single" or "joint" life Ins. I have put several hours into
this one formula; any suggestions? Please.

=IF(IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29)),AH!E19),
(IF(AND(NOT(ISBLANK(BU26)),(BX26="Single"),ISBLANK(BU29)),SL2!E19),IF(AND((NOT(ISBLANK(BU26))),(BX26="Joint"),ISBLANK(BU29)),JL2!E19),IF(AND((NOT(ISBLANK(BU26))),(BX26="Single"),NOT(ISBLANK(BU29))),'SL&AH2'!E19),IF(AND(NOT(ISBLANK(BU26)),(BX26="Joint"),NOT(ISBLANK(BU29))),'JL&AH2'!E19),
(IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19)))

Thanks.

Maybe like this:

=IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29))),AH!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Single",ISBLANK(BU29)),SL2!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Joint",ISBLANK(BU29)),JL2!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Single",NOT(ISBLANK(BU29))),'SL&AH2'!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Joint",NOT(ISBLANK(BU29))),'JL&AH2'!E19,IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19,""))))))

Hope this helps
Rowan

Bill R wrote:
> Why can't I get the formula below to work? There are 6 sheets in my
> worksheet, each of which calculates a payment with different options. The
> formula is located on a 7th sheet that is in the same workbook. The formula
> should pull the correct payment from the correct sheet based on the options
> selected by a customer who will put an "x" in one, or both of two boxes and
> choosing either "single" or "joint" life Ins. I have put several hours into
> this one formula; any suggestions? Please.
>
> =IF(IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29)),AH!E19),
> (IF(AND(NOT(ISBLANK(BU26)),(BX26="Single"),ISBLANK(BU29)),SL2!E19),IF(AND((NOT(ISBLANK(BU26))),(BX26="Joint"),ISBLANK(BU29)),JL2!E19),IF(AND((NOT(ISBLANK(BU26))),(BX26="Single"),NOT(ISBLANK(BU29))),'SL&AH2'!E19),IF(AND(NOT(ISBLANK(BU26)),(BX26="Joint"),NOT(ISBLANK(BU29))),'JL&AH2'!E19),
> (IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19)))
>
> Thanks.
>
>
>

I think this - but note you do not have a result for not meeting the last if
condition - maybe the last if is not needed.

=IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29))),AH!E19,
IF(AND(NOT(ISBLANK(BU26)),BX26="Single",ISBLANK(BU29)),SL2!E19,
IF(AND(NOT(ISBLANK(BU26 )),BX26="Joint",ISBLANK(BU29)),JL2!E19,
IF(AND(NOT(ISBLANK(BU26 )),BX26="Single",NOT(ISBLANK(BU29))),'SL&AH2'!E19,
IF(AND(NOT(ISBLANK(BU26)),BX26="Joint",NOT(ISBLANK(BU29))),'JL&AH2'!E19,
IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19))))))

"Rowan" wrote:

> Maybe like this:
>
> =IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29))),AH!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Single",ISBLANK(BU29)),SL2!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Joint",ISBLANK(BU29)),JL2!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Single",NOT(ISBLANK(BU29))),'SL&AH2'!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Joint",NOT(ISBLANK(BU29))),'JL&AH2'!E19,IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19,""))))))
>
> Hope this helps
> Rowan
>
> Bill R wrote:
> > Why can't I get the formula below to work? There are 6 sheets in my
> > worksheet, each of which calculates a payment with different options. The
> > formula is located on a 7th sheet that is in the same workbook. The formula
> > should pull the correct payment from the correct sheet based on the options
> > selected by a customer who will put an "x" in one, or both of two boxes and
> > choosing either "single" or "joint" life Ins. I have put several hours into
> > this one formula; any suggestions? Please.
> >
> > =IF(IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29)),AH!E19),
> > (IF(AND(NOT(ISBLANK(BU26)),(BX26="Single"),ISBLANK(BU29)),SL2!E19),IF(AND((NOT(ISBLANK(BU26))),(BX26="Joint"),ISBLANK(BU29)),JL2!E19),IF(AND((NOT(ISBLANK(BU26))),(BX26="Single"),NOT(ISBLANK(BU29))),'SL&AH2'!E19),IF(AND(NOT(ISBLANK(BU26)),(BX26="Joint"),NOT(ISBLANK(BU29))),'JL&AH2'!E19),
> > (IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19)))
> >
> > Thanks.
> >
> >
> >
>

Thanks Rowan, It almost works. Everything works except checking both boxes.
That gives me a #Ref error.

"Rowan" wrote:

> Maybe like this:
>
> =IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29))),AH!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Single",ISBLANK(BU29)),SL2!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Joint",ISBLANK(BU29)),JL2!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Single",NOT(ISBLANK(BU29))),'SL&AH2'!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Joint",NOT(ISBLANK(BU29))),'JL&AH2'!E19,IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19,""))))))
>
> Hope this helps
> Rowan
>
> Bill R wrote:
> > Why can't I get the formula below to work? There are 6 sheets in my
> > worksheet, each of which calculates a payment with different options. The
> > formula is located on a 7th sheet that is in the same workbook. The formula
> > should pull the correct payment from the correct sheet based on the options
> > selected by a customer who will put an "x" in one, or both of two boxes and
> > choosing either "single" or "joint" life Ins. I have put several hours into
> > this one formula; any suggestions? Please.
> >
> > =IF(IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29)),AH!E19),
> > (IF(AND(NOT(ISBLANK(BU26)),(BX26="Single"),ISBLANK(BU29)),SL2!E19),IF(AND((NOT(ISBLANK(BU26))),(BX26="Joint"),ISBLANK(BU29)),JL2!E19),IF(AND((NOT(ISBLANK(BU26))),(BX26="Single"),NOT(ISBLANK(BU29))),'SL&AH2'!E19),IF(AND(NOT(ISBLANK(BU26)),(BX26="Joint"),NOT(ISBLANK(BU29))),'JL&AH2'!E19),
> > (IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19)))
> >
> > Thanks.
> >
> >
> >
>

I made an error when copying your formula. When entered correctly it works
perfectly. Thank you very much.

"Rowan" wrote:

> Maybe like this:
>
> =IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29))),AH!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Single",ISBLANK(BU29)),SL2!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Joint",ISBLANK(BU29)),JL2!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Single",NOT(ISBLANK(BU29))),'SL&AH2'!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Joint",NOT(ISBLANK(BU29))),'JL&AH2'!E19,IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19,""))))))
>
> Hope this helps
> Rowan
>
> Bill R wrote:
> > Why can't I get the formula below to work? There are 6 sheets in my
> > worksheet, each of which calculates a payment with different options. The
> > formula is located on a 7th sheet that is in the same workbook. The formula
> > should pull the correct payment from the correct sheet based on the options
> > selected by a customer who will put an "x" in one, or both of two boxes and
> > choosing either "single" or "joint" life Ins. I have put several hours into
> > this one formula; any suggestions? Please.
> >
> > =IF(IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29)),AH!E19),
> > (IF(AND(NOT(ISBLANK(BU26)),(BX26="Single"),ISBLANK(BU29)),SL2!E19),IF(AND((NOT(ISBLANK(BU26))),(BX26="Joint"),ISBLANK(BU29)),JL2!E19),IF(AND((NOT(ISBLANK(BU26))),(BX26="Single"),NOT(ISBLANK(BU29))),'SL&AH2'!E19),IF(AND(NOT(ISBLANK(BU26)),(BX26="Joint"),NOT(ISBLANK(BU29))),'JL&AH2'!E19),
> > (IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19)))
> >
> > Thanks.
> >
> >
> >
>

You're welcome.

Bill R wrote:
> I made an error when copying your formula. When entered correctly it works
> perfectly. Thank you very much.
>
> "Rowan" wrote:
>
>
>>Maybe like this:
>>
>>=IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29))),AH!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Single",ISBLANK(BU29)),SL2!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Joint",ISBLANK(BU29)),JL2!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Single",NOT(ISBLANK(BU29))),'SL&AH2'!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Joint",NOT(ISBLANK(BU29))),'JL&AH2'!E19,IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19,""))))))
>>
>>Hope this helps
>>Rowan
>>
>>Bill R wrote:
>>
>>>Why can't I get the formula below to work? There are 6 sheets in my
>>>worksheet, each of which calculates a payment with different options. The
>>>formula is located on a 7th sheet that is in the same workbook. The formula
>>>should pull the correct payment from the correct sheet based on the options
>>>selected by a customer who will put an "x" in one, or both of two boxes and
>>>choosing either "single" or "joint" life Ins. I have put several hours into
>>>this one formula; any suggestions? Please.
>>>
>>>=IF(IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29)),AH!E19),
>>>(IF(AND(NOT(ISBLANK(BU26)),(BX26="Single"),ISBLANK(BU29)),SL2!E19),IF(AND((NOT(ISBLANK(BU26))),(BX26="Joint"),ISBLANK(BU29)),JL2!E19),IF(AND((NOT(ISBLANK(BU26))),(BX26="Single"),NOT(ISBLANK(BU29))),'SL&AH2'!E19),IF(AND(NOT(ISBLANK(BU26)),(BX26="Joint"),NOT(ISBLANK(BU29))),'JL&AH2'!E19),
>>>(IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19)))
>>>
>>>Thanks.
>>>
>>>
>>>
>>

Hi!

Try this:

=IF(AND(BU26="",BU29<>""),AH!E19,IF(AND(BU26<>"",BX26="Single",BU29=""),SL2!E19,IF(AND(BU26<>"",BX26="Joint",BU29=""),JL2!E19,IF(AND(BU26<>"",BX26="Single",BU29<>""),'SL&AH2'!E19,IF(AND(BU26<>"",BX26="Joint",BU29<>""),'JL&AH2'!E19,IF(AND(BU26="",BU29=""),None3!E19,""))))))

Biff

"Bill R" <[email protected]> wrote in message
news:[email protected]...
> Why can't I get the formula below to work? There are 6 sheets in my
> worksheet, each of which calculates a payment with different options. The
> formula is located on a 7th sheet that is in the same workbook. The
> formula
> should pull the correct payment from the correct sheet based on the
> options
> selected by a customer who will put an "x" in one, or both of two boxes
> and
> choosing either "single" or "joint" life Ins. I have put several hours
> into
> this one formula; any suggestions? Please.
>
> =IF(IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29)),AH!E19),
> (IF(AND(NOT(ISBLANK(BU26)),(BX26="Single"),ISBLANK(BU29)),SL2!E19),IF(AND((NOT(ISBLANK(BU26))),(BX26="Joint"),ISBLANK(BU29)),JL2!E19),IF(AND((NOT(ISBLANK(BU26))),(BX26="Single"),NOT(ISBLANK(BU29))),'SL&AH2'!E19),IF(AND(NOT(ISBLANK(BU26)),(BX26="Joint"),NOT(ISBLANK(BU29))),'JL&AH2'!E19),
> (IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19)))
>
> Thanks.
>
>
>