Issue with formula not reading from a specific cell

[tyshanklin]

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!

 

[jasonb75]

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:

Could you clarify that the points in bold above are correct, if they are then that will most likely be the cause of the problem. The formula in D2 can't change what is in E2. The formula in E2 would need to change what is in E2 based on the content of D2.

In addition, the formula that you have posted is not a valid excel formula, so we would need to know which program you are using. If it not excel then we may not be able to help.