• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to capture biggest and lowest value from a dynamic cell

kopsy

New Member
Respected Members,

I have attached a file below, please check,
In Calculation Sheet, I wanted to obtain the Highest High in "I" & Lowest low in "J" columns from the constantly changing cell value "G" .

Also i would like to know what are the various formulas for capturing HH & LL form a single dynamic cell. I got the reference for the same " https://chandoo.org/forum/threads/to-capture-highest-and-lowest-value.20669/ " but unfortunately this has much older version of excel, i couldn't see any formulas to take the benefit. My version of Office Excel is 2019.

Thank you,
kopsy
 

Attachments

  • Spread2.xlsm
    450.7 KB · Views: 7
Kopsy

Firstly, Welcome to the Chandoo.org Forums

You cannot capture the highest and Lowest values from a single changing cell using formula. You will need to use a VBA solution as described in that post

Now that I have said that somebody will probably post a formulaic solution
 
Thanks Hui, for welcoming and responding to my query.

Can you please add the the vba code in it? If its not possible to derive High and Low from single changing cell by using a formula, will it be possible using the same vba or with little modification to work for other columns and worksheets as well, to obtain the same H & L?

Thank you
 
Kopsy

Have a look at the code in the Futures worksheet module in VBA (Alt+F11)

The code executes when the data source updates from your Data Link

It should be easy to change that to work on other ranges
 

Attachments

  • Spread2 (IH).xlsm
    469.2 KB · Views: 14
Hi Hui,
Can you please check the file as it is not returning any value in the I & J columns, Calculation Sheet.
Also in VBA code why range A2:M477 is taken in the Futures Sheet, when the values to be derived from Calculation Sheet, G column. Please check, If i'm wrong somewhere, please guide me. Thank you.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim lr As Integer

Application.EnableEvents = False

Application.CalculateFull

If Application.Intersect(Target, Range("A2:M477")) Is Nothing Then Exit Sub

Worksheets("Calculation").Activate

lr = Range("G" & Rows.Count).End(xlUp).Row
For Each c In Range("G2:G" & lr)
  If c.Value > c.Offset(0, 2).Value Then
      c.Offset(0, 2).Value = c.Value
  ElseIf c.Value < c.Offset(0, 3).Value Then
      c.Offset(0, 3).Value = c.Value
  End If
Next

Application.CalculateFull

Application.EnableEvents = True

End Sub
 
Because Column G on the Calculation sheet doesn't change
Its value changes based on changes in Columns in the Futures sheet

I have guided you

I don't have access to the data to see what is causing the change
I assume it is a Data Link to a remote data source.

You need to understand what is changing and use that to trigger the calculation checking
 
Back
Top