Issue with formula not reading from a specific cell

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!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top