10-02-2020 07:34 AM
10-02-2020 07:34 AM
10-02-2020 07:45 AM
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.
10-02-2020 09:46 AM
@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
10-02-2020 10:16 AM
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?
10-02-2020 10:32 AM
@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"
10-02-2020 12:33 PM
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
10-03-2020 01:23 AM
10-03-2020 12:10 PM
If no dates in this and other columns, above formula returns With Sales
Or you mean something else?