Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using an IF Formula Mulitple Times in One Cell
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using an IF Formula Mulitple Times in One Cell
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 "Bob" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using an IF Formula Mulitple Times in One Cell
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"),"Packag e Complete","") Mike "Mike H" wrote: 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 "Bob" wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using an IF Formula Mulitple Times in One Cell
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"))) -- Rick (MVP - Excel) "Bob" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using an IF Formula Mulitple Times in One Cell
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"))) -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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"))) -- Rick (MVP - Excel) "Bob" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using an IF Formula Mulitple Times in One Cell
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. "Rick Rothstein" wrote: 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"))) -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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"))) -- Rick (MVP - Excel) "Bob" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using an IF Formula Mulitple Times in One Cell
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. -- Rick (MVP - Excel) "Bob" wrote in message ... 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. "Rick Rothstein" wrote: 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"))) -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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"))) -- Rick (MVP - Excel) "Bob" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing mulitple times to adobe | Excel Discussion (Misc queries) | |||
Mulitple Formulas/Same Cell | Excel Worksheet Functions | |||
Repeating a formula across mulitple columns | Excel Worksheet Functions | |||
Formula with mulitple condtions | Excel Worksheet Functions | |||
What is the formula for adding mulitple numbers in one excel cell. | Excel Discussion (Misc queries) |