Forum Discussion

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

Gold 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

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
Gold 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
  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...
  - mathetes
    Gold Contributor
    Jul 28, 2020
    shahzad_afzal
    
    I know it's clear in your mind. It's not in mine. A big part of the confusion in my mind is (I think) caused by Column E apparently being the final or more important part of the record, yet appearing between D and F...so it's that relationship and your reference to rows that appear to FOLLOW rather than precede (like why does E10 get a date from F17?)...
    
    Let me ask again, and I'll not confuse it by asking subordinate questions:
    
    Row by row, what's happening? Walk me though what's happening in each column, especially E, in each of the rows, 9 through 19.