Forum Discussion
Excel Formula Help
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...
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?
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...
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.
mathetes Row 9; a new addition of customer number ABC2000078304... Manual entry of Date in Column D... In column E, it will get date from D as its a new addition...
After 4 days; customer decided to lock it link temporary for any reason; a manual entry in column F...
As the status changed to Locked; added a new row to show customer went to lock and will stay in lock till the time a manual entry of activity end is added in column F against it...
After less than 1 hour; customer decided to be active again... Added a manual entry and activity of lock ended and customer moved to active state in row 11... same type of lock and active state will go on till the time customer decided in row 18 to terminate the link on 25th Mar... Active status ends with a manual entry in F17 and shows being terminated in E18...
1 Sec is a system delay to process the request...
If I run the lookup formula; like I shared in my first message; excel will get 25th mar date in E10 as the maximum date of any customer whereas I need the date of last instance in E10 which is 16th Mar 5:59:56...
Hope this is clear now...