Using an IF Formula Mulitple Times in One Cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob - 04 Oct 2008 13:40 GMT

I'm working on a project where cell B3 through D3 contain the values "Yes"
and "No." These indicate whether the name in A3 has the documents listed in
B2 through D2. If the value in a cell is "No" I would like for cell I3 to
show which documents each name needs. Finally, when all the information is
complete I would like for it to show "Packet Completed" until H3 states a
"Yes" answer, which then would show "Nothing Needed" For example:

Name Homework Quizzes Test Needs
John Yes No No Quizzes, Test

Say John finishes his quizzes then it would only show Tests in the Needs
Column, and when he finished his tests the needs column would read All
Assignments Completed.

I've tried nesting the IF formula, but it states "Argument too long" in a
message box. Again I nested 5 IF Formulas together. How would I be able to
have Excel work for me to fill in that information automatically as it is
updated?

Reply to this Message

Mike H - 04 Oct 2008 14:07 GMT

Bob,

Try this

=IF(B2="Yes","",$B$1&",")&IF(C2="Yes",""," "&$C$1&",")&IF(D2="Yes","","
"&$D$1)

You can also drag it down.

Mike

> I'm working on a project where cell B3 through D3 contain the values "Yes"
> and "No." These indicate whether the name in A3 has the documents listed in
[quoted text clipped - 14 lines]
> have Excel work for me to fill in that information automatically as it is
> updated?

Reply to this Message

Mike H - 04 Oct 2008 14:25 GMT

Bob,

Reading this again I'm confused.

>>If the value in a cell is "No" I would like for cell I3 to
> > show which documents each name needs.

>>Finally, when all the information is
> > complete I would like for it to show "Packet Completed" until H3 states a
> > "Yes" answer, which then would show "Nothing Needed" For example:

This is the closest I can get but I don't see where H3 comes into things.

=IF(B3="Yes","",$B$2&",")&IF(C3="Yes",""," "&$C$2&",")&IF(D3="Yes","","
"&$D$2)&IF(AND(B3="Yes",C3="Yes",D3="Yes"),"Package Complete","")

Mike

> Bob,
>
[quoted text clipped - 25 lines]
> > have Excel work for me to fill in that information automatically as it is
> > updated?

Reply to this Message

Rick Rothstein - 04 Oct 2008 19:00 GMT

Does this do what you want? Place in I3 and copy down....

=IF(H3="Yes","Nothing Needed",MID(LEFT(", "&B$2,10*(B3="No"))&LEFT(",
"&C$2,9*(C3="No"))&LEFT(", "&D$2,6*(D3="No")),3,99)&LEFT("Packet
Complete",15*(B3&C3&D3="YesYesYes")))

Signature

Rick (MVP - Excel)

> I'm working on a project where cell B3 through D3 contain the values "Yes"
> and "No." These indicate whether the name in A3 has the documents listed
[quoted text clipped - 15 lines]
> have Excel work for me to fill in that information automatically as it is
> updated?

Reply to this Message

Rick Rothstein - 04 Oct 2008 19:04 GMT

I see my newsreader broke the formula at the blank spaces making it hard to
realize the blanks are there. Here is the formula broken apart so the
newsreader won't do that...

=IF(H3="Yes","Nothing Needed",MID(LEFT(", "&B$2,10*(B3="No"))&
LEFT(", "&C$2,9*(C3="No"))&LEFT(", "&D$2,6*(D3="No")),3,99)&
LEFT("Packet Complete",15*(B3&C3&D3="YesYesYes")))

Signature

Rick (MVP - Excel)

> Does this do what you want? Place in I3 and copy down....
>
[quoted text clipped - 25 lines]
>> have Excel work for me to fill in that information automatically as it is
>> updated?

Reply to this Message

Bob - 05 Oct 2008 13:12 GMT

Thanks for the help, the last formula worked for me, except I had to change
the 6 where it says (", "&D$2,6*(D3="No")), to 8 to get it to show the entire
data. Thanks again.

> I see my newsreader broke the formula at the blank spaces making it hard to
> realize the blanks are there. Here is the formula broken apart so the
[quoted text clipped - 33 lines]
> >> have Excel work for me to fill in that information automatically as it is
> >> updated?

Reply to this Message

Rick Rothstein - 05 Oct 2008 16:57 GMT

LOL... I had noticed the 6/8 problem myself and I changed it... in my test
code, but I had forgotten that I had copied the original code into the
Clipboard **before** I made that change, so what I posted ended up being the
non-changed code. Sorry about that... but I am glad you were able to figure
out the problem on your own.

Signature

Rick (MVP - Excel)

> Thanks for the help, the last formula worked for me, except I had to
> change
[quoted text clipped - 48 lines]
>> >> is
>> >> updated?

Reply to this Message