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

#### sarao18592

##### New Member
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.

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### mart37

##### Board Regular
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
ABCDEFGH
11601-Feb-21above30
22402-Feb-21
34503-Feb-21Method 1CountMethod 2
41204-Feb-2103-Feb-21205-Feb-21
53705-Feb-2105-Feb-2103-Feb-21
61606-Feb-21
72107-Feb-21
82808-Feb-21
Sheet1
Cell Formulas
RangeFormula
E4:E5E4=FILTER(B:B,(A:A>E1))
G4G4=COUNTIF(A:A,">"&E1)
H4:H5H4=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

##### New Member
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.

Replies
6
Views
118
Replies
21
Views
345
Replies
7
Views
262
Replies
1
Views
133
Replies
5
Views
279

1,130,042
Messages
5,639,725
Members
417,108
Latest member
Thein Than

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back