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
mathetes's avatar
mathetes
Silver Contributor
Jul 28, 2020

shahzad_afzal 

 

You clearly know about absolute and relative cell references, which would be part of the answer. But what do you do if you're in Cell C345? Is there always only a difference of 5 rows between the start and finish row for that formula you're asking about.

 

In other words, what we need from you is a bit more information on the larger context here...how is the table being built?

 

By the way, another  (and I think clearer) way to add a second to the value of another date&time cell is to use +TIME(0,0,1)

 

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

Resources