Hi Guys,
Columns B, C, D, and E contain a list look-up (Yes or No)
Earlier today, you guys helped me that if column B and/ or E has "Yes", to return certain text (Thank you!)
I placed the updated functionality in column F.
Then I wanted the user to identify the scope of work based on the input provided. A "Yes" answer in columns B and/ or E would require one set of docs while
a "No" answer in columns C and D would require a second set of docs.
I could not figure out how to combine the two scenarios, so I merged columns A-F from two rows into one row and
then I added =IF(OR(B17="No",C17="No",D17="No",E17="No")," ","Update docs")
However, Would like to address a couple of things and need your help.
1. Is it possible to combine/ nest the documents (column G) needed based on the input validation (columns B-E)? If so, can you help?
2. If columns C or D contain a "Yes", I would the validation statement to be true (and right now it is not robust enough only some rows are true
3. Can the cells in column F and G remain blank (not visible) if any/ all the data in columns B, C, D, or E are blank?
Thanks,
LizzyG
Columns B, C, D, and E contain a list look-up (Yes or No)
Earlier today, you guys helped me that if column B and/ or E has "Yes", to return certain text (Thank you!)
I placed the updated functionality in column F.
Then I wanted the user to identify the scope of work based on the input provided. A "Yes" answer in columns B and/ or E would require one set of docs while
a "No" answer in columns C and D would require a second set of docs.
I could not figure out how to combine the two scenarios, so I merged columns A-F from two rows into one row and
then I added =IF(OR(B17="No",C17="No",D17="No",E17="No")," ","Update docs")
However, Would like to address a couple of things and need your help.
1. Is it possible to combine/ nest the documents (column G) needed based on the input validation (columns B-E)? If so, can you help?
2. If columns C or D contain a "Yes", I would the validation statement to be true (and right now it is not robust enough only some rows are true
3. Can the cells in column F and G remain blank (not visible) if any/ all the data in columns B, C, D, or E are blank?
Thanks,
LizzyG
| LizzyG Validation.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Change # | Create a new Workflow? | Use Existing Materials? | Use Existing Equipment? | Modify User Groups? | Requires SMART doc? | Document Updates Required for Change include: | ||
| 2 | 1 | Yes | Yes | Yes | Yes | Yes | SOP Updates, UnitOps Updates, Check User Group Permissions | ||
| 3 | |||||||||
| 4 | 2 | Yes | No | No | No | Yes | SOP Updates, UnitOps Updates, Check User Group Permissions | ||
| 5 | |||||||||
| 6 | 3 | Yes | Yes | No | No | Yes | SOP Updates, UnitOps Updates, Check User Group Permissions | ||
| 7 | |||||||||
| 8 | 4 | Yes | Yes | Yes | No | Yes | SOP Updates, UnitOps Updates, Check User Group Permissions | ||
| 9 | Complete the Req for Modification Form, Master Data Document, and Configuration Specification | ||||||||
| 10 | 5 | No | No | No | Yes | Yes | SOP Updates, UnitOps Updates, Check User Group Permissions | ||
| 11 | |||||||||
| 12 | 6 | No | No | Yes | No | No | |||
| 13 | |||||||||
| 14 | 5 | No | No | No | Yes | Yes | SOP Updates, UnitOps Updates, Check User Group Permissions | ||
| 15 | |||||||||
| 16 | 6 | No | |||||||
| 17 | Complete the Req for Modification Form, Master Data Document, and Configuration Specification | ||||||||
Sheet1 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2,F4,F6,F8,F10,F12,F14,F16 | F2 | =IF(OR(B2="Yes",E2="Yes"),"Yes","No") |
| G2,G4,G6,G8,G10,G12,G14,G16 | G2 | =IF(F2="Yes","SOP Updates, UnitOps Updates, Check User Group Permissions"," ") |
| G3,G5,G7,G9,G11,G13,G15,G17 | G3 | =IF(OR(B2="No",C2="No",D2="No",E2="Yes")," ","Complete the Req for Modification Form, Master Data Document, and Configuration Specification") |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| B2:E17 | List | Yes, No |
