Forum Discussion

shahzad_afzal's avatar
shahzad_afzal
Copper Contributor
Jul 28, 2020

Excel Formula Help

Hi; I am using a lookup formula to get the last date of any reference number and then add one sec to it... =IF(D10="New Addition",E10,LOOKUP(3,(1/($C$4:$C$54=C10)+(1/$G$4:$G$54<>"")),$G$4:$G$54)+1/8...
excel
shahzad_afzal's avatar
shahzad_afzal
Copper Contributor
Jul 28, 2020

mathetes I am attaching a test case for you to review...

When you sort data with the date which I have to, every thing will get disturbed...

I just want the highest date till the selected row and not after that to be used to get dates; else every time; highest date is displayed from the column...

mathetes's avatar
mathetes
Silver Contributor
Jul 28, 2020

shahzad_afzal 

 

I am not following you at all...that is, I don't understand what you're trying to do with the sample spreadsheet; your words in this last post don't fully make sense either.

 

When you sort data with the date which I have to, every thing will get disturbed...

  • Sort?
  • Sort data with the date which I have to? ("have to" meaning "must do"?)
  • everything will get disturbed? (supposed to get disturbed; will in the course of "sorting")

 

I just want the highest date till the selected row and not after that to be used to get dates; else every time; highest date is displayed from the column...

  • Which column(s) are you referring to? There are five columns in your example; three of them are dates AND times.
  • What is supposed to happen with column E? How do column D and F affect that.
    • Please answer that question in words....I know that's what your formula is trying to do, and it's what your question is about. But spell it out more completely.
  • shahzad_afzal's avatar
    shahzad_afzal
    Copper Contributor
    Jul 28, 2020

    mathetes sorry for the confusions...

    Column E will have two type of dates; one if new addition and date will come from Column D... When activity ends; next time will have one second addition... Column E will get date & time from Column D for all activities other than new addition...

    Now come to Column B & Row 10; ABC2000078304... If i will copy paste formula; i will get highest date from column F which is F17 Cell... I want it to get date with reference to cell F10 only...

     

    Hope this makes sense...

    • mathetes's avatar
      mathetes
      Silver Contributor
      Jul 28, 2020

      shahzad_afzal 

       

      Sorry. Still not making sense.

       

      Walk me though what's happening in each column in rows 9 through 19.

      • The two entries "New Addition" and "Termination" make sense.
      • What does "Locked" mean? what does "Active" mean?
      • And how does each event affect the various time stamps?
      • shahzad_afzal's avatar
        shahzad_afzal
        Copper Contributor
        Jul 28, 2020

        mathetes A customer joined on new addition... After some time; customer wants to lock his internet link or terminate when not needed... Scenario 1; customer ID ABC2000078304 (Cell B9); came on network... Stayed active from 12th Mar till 16th Mar and went to lock...

        The moment i run lookup; E10 will get date from F17 which is the highest date in the column... It has to be 16th Mar not 25th Mar...

        Just copy paste formula from E5 onwards till E28, you will see that column E will get populated with highest date record from column F...

        I want ABC2000078304 related record, whatever row number it is; will get date from the previous record only, rather than max date from column F... 

Resources