If function in tracking status of my milestones

[waqasahmed85]

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

[#2]

@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.

[#3]

@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

[#4]

@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?

[#5]

@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

[#6]

@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

[#7]

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

[#8]

@waqasahmed85 

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

Or you mean something else?

[#9]

Yes I meant precisely that

Thanks Sergei

[#10]

@waqasahmed85 , glad to help

[#11]

@Sergei Baklan I have developed the sheet in much better way ever since you helped me. Only the duplicate sheet I created for the same purpose but different business requires me to go through the same track. However, in my cell C2 in tab 'SL Order Tracking' the index function is picking up dates from all functions except from AR2 even though it is defined through the Index function (BA2:BA11) in cell C2 as BA11. 

Also, in B2 of the tab, I want my argument function to respond blank if C2 reflects "With Sales; respond "Open" if the cell C2 reflects any other value from the index BA2:BA10 but if the cell C2 reflects value in BA11 which is "Check Received", I want my cell B2 to reflect "Closed". I figured if this would work good through multiple IFS function but its not really responding to my requirement. Can you please help?

Thanks