Give date when dynamic range exceeds value for only the first time

[sarao18592]

This is my first post here so i apologize if I have not provided the appropriate information! I would love to be able to get help with my current problem!

Column A contains a varied range of values which are linked to another sheet. Hence when that sheet changes, the values in Column A change.
In column B, I would like to know the date when the Values in Column A exceed 30. The values in column A will change every 12 hours and will only irregularly increase over time.

Furthermore, in column B, I would like only the value when it exceeds for the first time. i.e. if A2 > 30 for the first time on 4th March then when the data refreshes on 5th march, B2 will still show 4th March. and I would like to repeat the same for the rest of the column.

Note: Each A value has a different start point and will increase irregularly, hence the date in the B column will be different.

 

[mart37]

Lock-in TODAY date?

Is there any way to automatically lock in the date after you pull it up with the TODAY function? Or is there another function that will do what I'm trying to do? I want it to automatically fill in today's date, when a certain empty cell has a value put in, then freeze there. Thanks in advance!

www.mrexcel.com

 

[mamady]

This is my first post here so i apologize if I have not provided the appropriate information! I would love to be able to get help with my current problem!

Column A contains a varied range of values which are linked to another sheet. Hence when that sheet changes, the values in Column A change.
In column B, I would like to know the date when the Values in Column A exceed 30. The values in column A will change every 12 hours and will only irregularly increase over time.

Furthermore, in column B, I would like only the value when it exceeds for the first time. i.e. if A2 > 30 for the first time on 4th March then when the data refreshes on 5th march, B2 will still show 4th March. and I would like to repeat the same for the rest of the column.

Note: Each A value has a different start point and will increase irregularly, hence the date in the B column will be different.

It would easier if you share a data set sample via XL2BB

If I understood your requirement, there are 3 ways of doing so:

1- if your office version is Office 365, use Filter formula
2- if it is not. then either the below proposed long formula (drag down as per the count number)
3- alternativly, create a pivot table, apply a filter and refresh the table whenever new data are entered/replaced in Column A

Book1
	A	B	C	D	E	F	G	H
1	16	01-Feb-21		above	30
2	24	02-Feb-21
3	45	03-Feb-21			Method 1		Count	Method 2
4	12	04-Feb-21			03-Feb-21		2	05-Feb-21
5	37	05-Feb-21			05-Feb-21			03-Feb-21
6	16	06-Feb-21
7	21	07-Feb-21
8	28	08-Feb-21
Sheet1

Cell Formulas
Range	Formula
E4:E5	E4	=FILTER(B:B,(A:A>E1))
G4	G4	=COUNTIF(A:A,">"&E1)
H4:H5	H4	=IFERROR(INDEX($B:$B,AGGREGATE(14,4,IFERROR(ROW(A:A)/(A:A>$E$1),0),COUNTA($A$1:A1))),"")
Dynamic array formulas.

Regards

 

[sarao18592]

Hi, Thank you for the response!
I will try to share the excel sheet in a few hours. Column B currently does not contain any dates and therefore this might not work well. Instead Column B is a blank.
Column A is dependent on another sheet, that sheet changes every 12 hours and the corresponding values in Column A change every 12 hours as well.

In column B, I need the date when its respective row in Column A first crosses 30, and then this value in column B should not change thereafter.