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/86400)Its is doing good... What I need is to restrict this function to cells in column before cell formula is running... Example: formula has to look at C10 and get max date; it will only use data from C4:C9... Data below it, don't want to use...

shahzad_afzal I think I understand what is trying to be done here. I agree with mathetes that it might be a good practice to set up the sheet as a database input sheet and then use pivot table to display the data in the desired format. But to shahzad_afzal question I believe you are looking for the formula in the original post to be corrected to work and that would be to use partial absolute and relative references similar to what is being used in the sample sheet you provided. so in the sample sheet row 10 you have:=IFERROR(IF(C10="New Addition",D10,LOOKUP(B10,$B$4:B9,$F$4:F9)+1/86400),"")notice how it has absolute reference for $B$4 but relative reference for B9I believe you want to switch to a formula that looks like the one you posted so you need to have a similar mix of absolute and relative references (modified to work on row 10 of sample sheet)=IF(C10="New Addition",D10,LOOKUP(3,(1/(B$4:B9=B10)+(1/F$4:F9<>"")),F$4:F9)+1/86400)that said you might also consider another variation that I find easier to read:=IF(C10="New Addition",D10,MAXIFS($F$3:$F9,$B$3:$B9,$B10)+TIME(,,1)) I am also not clear about what your problem actually is since the formula you already have on the sheet appears to be working but hopefully these variations might help. If not please explain this problem with sorting and it getting mixed up. EDIT: I just had a thought if you are moving things around and such and having issues with finding the "correct" max previous end time then just add the whole table to the MAXIFS and add another condition that the END TIME must be less than the END TIME from this row but had to add another IF condition for the "Termination" to just use the max time because there is no END TIME in that row:=IFS(C4="New Addition",D4,C4="Termination",MAXIFS(F:F,B:B,B4)+TIME(,,1),TRUE,MAXIFS(F:F,B:B,B4,F:F,"<"&F4)+TIME(,,1))

shahzad_afzal I am still curious about the 3 equations I presented in my attachment previously and if they work or what doesn't work. Also, I pointed out that the first row had a start date after the end date and was wondering if that was just a typo in the sample or something that should be expected in this data set.

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)

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

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.

Excel Formula Help | Microsoft Community Hub

30 Replies

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
  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...
  Test Case.xlsx12 KB
  - 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.

Forum Discussion

Excel Formula Help

30 Replies

Resources