If function in tracking status of my milestones

%3CLINGO-SUB%20id%3D%22lingo-sub-1739513%22%20slang%3D%22en-US%22%3EIf%20function%20in%20tracking%20status%20of%20my%20milestones%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1739513%22%20slang%3D%22en-US%22%3EHi%3A%3CBR%20%2F%3EI%20really%20need%20help%20in%20developing%20an%20if%20function%20syntax%20on%20my%20sheet%20to%20track%20status%20of%20milestone%20dates%20in%20my%20excel%20sheet.%20I%20have%208%20milestone%20which%20are%20to%20be%20recorded%20as%20I%20achieve%20those%20milestone%20dates.%20It%20goes%20so%20that%20every%20time%20I%20put%20in%20a%20certain%20date%20I%20want%20my%20status%20cell%20to%20return%20me%20the%20string%20value%20I%E2%80%99ve%20assigned%20to%20it.%20I%E2%80%99m%20currently%20using%3A%3CBR%20%2F%3EIf(E20%2Cif(L20%2C%E2%80%9DCC%E2%80%9D%2C%E2%80%9DBB%E2%80%9D)%2C%E2%80%9CAA%E2%80%9D)%3CBR%20%2F%3EThis%20is%20working%20fine%20for%20my%20first%20three%20milestones%20AA%2C%20BB%20and%20CC%20but%20not%20going%20any%20further%20than%20this.%3CBR%20%2F%3EDate%20values%20are%207%20in%20my%20sheet%20btw%2C%20I%20have%20to%20have%20AA%20in%20my%20default%20as%20my%201st%20milestone%20as%20you%20can%20understand.%20If%20no%20date%20values%20are%20inserted%20my%20first%20milestone%20should%20show%20AA%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1739513%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1739571%22%20slang%3D%22en-US%22%3ERe%3A%20If%20function%20in%20tracking%20status%20of%20my%20milestones%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1739571%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F817861%22%20target%3D%22_blank%22%3E%40waqasahmed85%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20may%20generate%20small%20sample%20file%20to%20illustrate%20the%20question%2C%20without%20that%20it's%20not%20clear%20what%20is%20needed.%20That%20not%20necessary%20could%20be%20IF()%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1740008%22%20slang%3D%22en-US%22%3ERe%3A%20If%20function%20in%20tracking%20status%20of%20my%20milestones%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1740008%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThe%20file%20is%20attached%20for%20your%20reference.%20The%20cell%20of%20interest%20here%20is%20D22%2C%20where%20the%20default%20cell%20is%20required%20to%20be%20%22with%20sales%22.%20My%20milestones%20are%20mentioned%20from%20F22%20to%20F29.%20Ideally%20I'm%20trying%20to%20link%20D22%20cells%20in%20my%20%22Order%20Status%22%20column.%20Your%20help%20is%20appreciated%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1740082%22%20slang%3D%22en-US%22%3ERe%3A%20If%20function%20in%20tracking%20status%20of%20my%20milestones%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1740082%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F817861%22%20target%3D%22_blank%22%3E%40waqasahmed85%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you%20for%20the%20file%2C%20but%20the%20logic%20is%20still%20unclear%20for%20me.%20You%20have%20list%20of%20orders%2C%20each%20of%20them%20could%20have%203%20statuses.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBut%20D22%20works%20only%20with%20Order%20%236%2C%20shall%20it%20be%20the%20same%20for%20each%20other%20order%20(i.e.%20same%20logic%20in%20separate%20column%20for%20each%20order)%20or%20you%20take%20into%20account%20only%20last%20order%3F%3C%2FP%3E%0A%3CP%3EWhat%20is%20relationship%2C%20if%20D22%20returns%20Check%20Received%20shall%20B20%20be%20changed%20on%20Closed.%20or%20in%20opposite%2C%20if%20Closed%20we%20return%20something%20in%20D22%3F%3C%2FP%3E%0A%3CP%3EYou%20have%207%20milestones%20but%20logic%20is%20more%20or%20less%20defined%20only%20for%203%20of%20them.%20When%2C%20for%20example%2C%20we%20shall%20indicate%20%22Shipment%20in%20Transit%22%2C%20when%20what%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1740125%22%20slang%3D%22en-US%22%3ERe%3A%20If%20function%20in%20tracking%20status%20of%20my%20milestones%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1740125%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BOk%2C%20D22%20is%20cell%20I'm%20trying%20to%20work%20my%20syntax%20on.%20To%20me%20its%20a%20template%20until%20I%20place%20it%20in%20B3%20and%20spread%20over%20the%20column.%20The%20reason%20its%20working%20on%20Order%23%206%20only%20is%20because%20I%20took%20that%20order%20as%20an%20example%20for%20myself%20while%20trying%20and%20trying%20to%20figure%20how%20can%20I%20get%20my%20milestones%20dates%20connected%20to%20this%20status%20cell.%20Hence%2C%20B20%20and%20the%20column%20associated%20will%20be%20totally%20replaced.%3C%2FP%3E%3CP%3EYou%20see%20I%20want%20to%20have%20my%20milestones%20placed%20as%3A%3C%2FP%3E%3CP%3EOrder%20Status%3A%20With%20Sales%2C%20PR%20Pending%2C%20PR%20created%2C%20....%2C%20Check%20Recieved%3C%2FP%3E%3CP%3EInstead%20of%20order%20status%3A%20Open%2C%20Closed%20Disputed%20(the%20drop%20down%20list)%3C%2FP%3E%3CP%3EThe%20idea%20for%20doing%20that%20is%20to%20have%20my%20management%20realize%20in%20one%20snapshot%20that%20where%20my%20orders%20are%20in%20terms%20of%20milestones.%3C%2FP%3E%3CP%3EShipment%20in%20transit%20is%20W20%2C%20when%20I%20put%20my%20date%20there%2C%20the%20status%20should%20reflect%2C%20%22Shipment%20in-transit%22%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1740555%22%20slang%3D%22en-US%22%3ERe%3A%20If%20function%20in%20tracking%20status%20of%20my%20milestones%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1740555%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F817861%22%20target%3D%22_blank%22%3E%40waqasahmed85%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20see%2C%20thank%20you%2C%20that%20was%20not%20easy%20to%20make%20a%20guess%20which%20columns%20to%20take.%20As%20variant%20formula%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(%24F%2421%3A%24F%2429%2C%0A%20%20%20%201%2B%0A%20%20%20%20(E20%26gt%3B1)%2B%0A%20%20%20%20(L20%26gt%3B1)%2B%0A%20%20%20%20(T20%26gt%3B1)%2B%0A%20%20%20%20(V20%26gt%3B1)%2B%0A%20%20%20%20(W20%26gt%3B1)%2B%0A%20%20%20%20(Z20%26gt%3B1)%2B%0A%20%20%20%20(AA20%26gt%3B1)%2B%0A%20%20%20%20(AC20%26gt%3B1)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EPlease%20see%20in%20I22%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor
Hi:
I really need help in developing an if function syntax on my sheet to track status of milestone dates in my excel sheet. I have 8 milestone which are to be recorded as I achieve those milestone dates. It goes so that every time I put in a certain date I want my status cell to return me the string value I’ve assigned to it. I’m currently using:
If(E20,if(L20,”CC”,”BB”),“AA”)
This is working fine for my first three milestones AA, BB and CC but not going any further than this.
Date values are 7 in my sheet btw, I have to have AA in my default as my 1st milestone as you can understand. If no date values are inserted my first milestone should show AA
9 Replies
Highlighted

@waqasahmed85 

Perhaps you may generate small sample file to illustrate the question, without that it's not clear what is needed. That not necessary could be IF() function.

Highlighted

@Sergei Baklan The file is attached for your reference. The cell of interest here is D22, where the default cell is required to be "with sales". My milestones are mentioned from F22 to F29. Ideally I'm trying to link D22 cells in my "Order Status" column. Your help is appreciated

Thanks

Highlighted

@waqasahmed85 

Thank you for the file, but the logic is still unclear for me. You have list of orders, each of them could have 3 statuses. 

But D22 works only with Order #6, shall it be the same for each other order (i.e. same logic in separate column for each order) or you take into account only last order?

What is relationship, if D22 returns Check Received shall B20 be changed on Closed. or in opposite, if Closed we return something in D22?

You have 7 milestones but logic is more or less defined only for 3 of them. When, for example, we shall indicate "Shipment in Transit", when what?

Highlighted

@Sergei Baklan Ok, D22 is cell I'm trying to work my syntax on. To me its a template until I place it in B3 and spread over the column. The reason its working on Order# 6 only is because I took that order as an example for myself while trying and trying to figure how can I get my milestones dates connected to this status cell. Hence, B20 and the column associated will be totally replaced.

You see I want to have my milestones placed as:

Order Status: With Sales, PR Pending, PR created, ...., Check Recieved

Instead of order status: Open, Closed Disputed (the drop down list)

The idea for doing that is to have my management realize in one snapshot that where my orders are in terms of milestones.

Shipment in transit is W20, when I put my date there, the status should reflect, "Shipment in-transit"

Thanks

Highlighted

@waqasahmed85 

I see, thank you, that was not easy to make a guess which columns to take. As variant formula could be

=INDEX($F$21:$F$29,
    1+
    (E20>1)+
    (L20>1)+
    (T20>1)+
    (V20>1)+
    (W20>1)+
    (Z20>1)+
    (AA20>1)+
    (AC20>1)
)

Please see in I22

Highlighted
Many thanks for the response. I was also hoping if no dates are placed in “hand over date from sales” column the cell reflects “With Sales” as default
Can you please help there as well
Highlighted

@waqasahmed85 

If no dates in this and other columns, above formula returns With Sales

image.png

Or you mean something else?

Highlighted
Yes I meant precisely that

Thanks Sergei
Highlighted