Oct 02 2020 07:34 AM
Oct 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.
Oct 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
Thanks
Oct 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?
Oct 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"
Thanks
Oct 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
Oct 03 2020 01:23 AM
Oct 03 2020 12:10 PM
If no dates in this and other columns, above formula returns With Sales
Or you mean something else?
Oct 03 2020 12:27 PM
Oct 03 2020 01:53 PM
@waqasahmed85 , glad to help
Dec 14 2020 12:12 AM
@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