Churchy LaFemme
Board Regular
- Joined
- Sep 22, 2010
- Messages
- 135
This nested if in a shared workbook is getting unwieldly. The one thing I can do, what I hope to do, is update the IF statement in Column I, Status, so that if always works.
The status text strings are found in the cells above columns 0 though P. (That is, the if refences are to O2, P2, etc.) Data inputs are always dates.
There are two basic flows – Monkey is from the US, or not from the US.
If from the US, we skip two steps.
There are two variables.
1. A non-US monkey may have quarantine set right away, set during reviews process, or it may already have happened and the “Monkey in Quarantine status will never appear. (One of the current problems is that if someone inputs the date that quarantine was set, the row will immediately show a later stage status, making it appear we have completed more steps than we have.
2. Assessments may be resubmitted.
– Either the director returns an assessment with questions, and we resubmit after addressing questions - Or we may realize we need to update and then resubmit resubmit (We’ve had problems where once something has been returned or resubmitted, that status sticks in “Director Reviewing”
First step is that once we have a date in Column, the first status should be triggered for that row. That is, one we input a date in column O, a status appears in column I. Column U may also be completed at the point, which is where this started falling apart. (The table is actually A-AL, but only columns that are part of this beastly IF are shown.)
Ignore the top row and the left-hand column. Those are not part of the table in my worksheet. Uploads are disabled (work) so I had to paste from Excel.
If there is a good tutorial somewhere that explains how to structure Nested IF statements to this level of complexity, that would be helpful.
I'm hoping someone has already build such a creature, and can provide the blueprint.
The status text strings are found in the cells above columns 0 though P. (That is, the if refences are to O2, P2, etc.) Data inputs are always dates.
There are two basic flows – Monkey is from the US, or not from the US.
If from the US, we skip two steps.
There are two variables.
1. A non-US monkey may have quarantine set right away, set during reviews process, or it may already have happened and the “Monkey in Quarantine status will never appear. (One of the current problems is that if someone inputs the date that quarantine was set, the row will immediately show a later stage status, making it appear we have completed more steps than we have.
2. Assessments may be resubmitted.
– Either the director returns an assessment with questions, and we resubmit after addressing questions - Or we may realize we need to update and then resubmit resubmit (We’ve had problems where once something has been returned or resubmitted, that status sticks in “Director Reviewing”
First step is that once we have a date in Column, the first status should be triggered for that row. That is, one we input a date in column O, a status appears in column I. Column U may also be completed at the point, which is where this started falling apart. (The table is actually A-AL, but only columns that are part of this beastly IF are shown.)
Ignore the top row and the left-hand column. Those are not part of the table in my worksheet. Uploads are disabled (work) so I had to paste from Excel.
| Complete Intake | Director Reviewing Assessment | Address Questions | Director Reviewing Assessment | Vet Finalizing Assessment | Monkey in Quarantine | Monkey Introduced to Group | Monkey Acclimating | Director Determination | Determination | |||
| Column A | Column I | Column O | Column P | Column Q | Column R | Column S | Column T | Column U | Column V | Column Q | Column X | |
| COUNTRY | STATUS | Monkey Arrived | Assessment Submitted | Director Returns w Questions | Resubmit Assessment | Director Approve | Vet Sets Quarantine | Monkey Completes Quarantine | Staff Manages & Monitors | Determination - Sanctuary or Rehome | Monkey Set for Stay or Rehome | |
| US | Complete Intake | DATA | IGNORE - may or may not have data | |||||||||
| Case 1 | Director Reviewing Assessment | DATA | ||||||||||
| Case 2 | Director Reviewing Assessment | DATA | DATA | |||||||||
| Address Questions | DATA | DATA | IGNORE - may or may not have data | |||||||||
| Vet Finalizing Assessment | DATA | IGNORE - may or may not have data | ||||||||||
| Monkey in Quarantine | DATA | BLANK | ||||||||||
| Case 1 | Monkey Introduced to Group | DATA | DATA | |||||||||
| Case 2 | Skip if Column A is "US" | IGNORE if Column A is "US" | ||||||||||
| Case 1 | Monkey Acclimating | DATA | ||||||||||
| Case 2 | Skip if Column A is "US" | IGNORE if Column A is "US" | ||||||||||
| Director Determination | DATA | |||||||||||
| Determination | DATA |
If there is a good tutorial somewhere that explains how to structure Nested IF statements to this level of complexity, that would be helpful.
I'm hoping someone has already build such a creature, and can provide the blueprint.
