tyshanklin
New Member
- Joined
- Jun 17, 2014
- Messages
- 9
Hi all,
I use a spreadsheet as a tracking doc to keep track of our process from project upload through client approval. The way the doc is set up is Column A (Name) has the name of task. Column B (OMIT) is where we log if a task has been omitted. Column D (Code) host the code I need help with, while the information from this code shows up in Column E (Status). Column F (Assignee) shows who is assigned the task, and G (Version) shows which version number the task is on.
Once each step has been completed, the responsible party will click on the corresponding cell in J - O (J = Uploaded, K = Ready for Review, L = Internally Approved, N = Sent for (Client) Approval, O = Client Approved), and hit the space bar (which will change the color of the cell to reflect that step is completed).
The formula I have works perfectly, except for the OMITs. When written out as a paraphrased logical statement, the formula should work like:
If column B says "Omit", column E should change to "OMIT". If column B says "Omit", and F is not blank change to "Assigned". If F and G have values change to "Working". If F and G have values, and J has a space, change to "Uploaded". If F and G have values, and J and K have spaces, change to "Ready for Review". This will continue until J-O have spaces, and Column E will change to "Client Approved". If none of this is true, Column E should say "Not Assigned".
The code does not change Column E to OMIT if Column B says "OMIT" - it will only change when column F says OMIT... And I really don't understand why that is.
Here is the formula placed in D2:
={"", if(LEN(F2) , (if(and(LEN(G2),J2="",K2="",L2="",N2="",O2=""),"Working",if(and(B2="OMIT",J2="",K2="",L2="",N2="",O2=""),"OMIT",if(and(J2=" ",K2="",L2="",N2="",O2=""),"Uploaded",if(and(J2=" ",K2=" ",L2="",N2="",O2=""),"Ready for Review",if(and(J2=" ",K2=" ",L2=" ",N2="",O2=""),"Int. Approved",if(and(J2=" ",K2=" ",L2=" ",N2=" ",O2=""),"Sent for Approval",if(and(J2=" ",K2=" ",L2=" ",N2=" ",O2=" "),"Client Approved" , "Assigned")))))))) , "Not Assigned")}
Any help in understanding why it reads F2 instead of B2 would be greatly appreciated!
I use a spreadsheet as a tracking doc to keep track of our process from project upload through client approval. The way the doc is set up is Column A (Name) has the name of task. Column B (OMIT) is where we log if a task has been omitted. Column D (Code) host the code I need help with, while the information from this code shows up in Column E (Status). Column F (Assignee) shows who is assigned the task, and G (Version) shows which version number the task is on.
Once each step has been completed, the responsible party will click on the corresponding cell in J - O (J = Uploaded, K = Ready for Review, L = Internally Approved, N = Sent for (Client) Approval, O = Client Approved), and hit the space bar (which will change the color of the cell to reflect that step is completed).
The formula I have works perfectly, except for the OMITs. When written out as a paraphrased logical statement, the formula should work like:
If column B says "Omit", column E should change to "OMIT". If column B says "Omit", and F is not blank change to "Assigned". If F and G have values change to "Working". If F and G have values, and J has a space, change to "Uploaded". If F and G have values, and J and K have spaces, change to "Ready for Review". This will continue until J-O have spaces, and Column E will change to "Client Approved". If none of this is true, Column E should say "Not Assigned".
The code does not change Column E to OMIT if Column B says "OMIT" - it will only change when column F says OMIT... And I really don't understand why that is.
Here is the formula placed in D2:
={"", if(LEN(F2) , (if(and(LEN(G2),J2="",K2="",L2="",N2="",O2=""),"Working",if(and(B2="OMIT",J2="",K2="",L2="",N2="",O2=""),"OMIT",if(and(J2=" ",K2="",L2="",N2="",O2=""),"Uploaded",if(and(J2=" ",K2=" ",L2="",N2="",O2=""),"Ready for Review",if(and(J2=" ",K2=" ",L2=" ",N2="",O2=""),"Int. Approved",if(and(J2=" ",K2=" ",L2=" ",N2=" ",O2=""),"Sent for Approval",if(and(J2=" ",K2=" ",L2=" ",N2=" ",O2=" "),"Client Approved" , "Assigned")))))))) , "Not Assigned")}
Any help in understanding why it reads F2 instead of B2 would be greatly appreciated!