Conditional Data Capture from link 2

[altaratz]

I'm in desperate need of help - for a project for my company that I need to get done.

I have a spreadsheet linked to a dynamic data source with cell values changing realtime.

I need to capture the value of the data feed if the data meets a certain criteria - so for example:

The price feed is for say stock quotes and cell A1 is the live feed for Microsoft stock price:

I want cell B1 to capture every price over $20 that the Microsoft stock price goes, like if it gets to $20.50 cell B1 would equal $20.50, but if it gets up to 20.75, then B1 would be 20.75, and it would stay 20.75 even if the stock goes back down (basically it captures the highest value that the data link prints)

What function or otherwise would I associate with cell B1 to do this.

I really appreciate this! - Please help!!!!

Ethan Altaratz

 

[xlbo]

Ok - this may send other calculations a bit flaky but here is what you need to do

In B1, enter
=IF(A1>B1,A1,B1)
You will get a messagebox stating that this is a circular reference - ignore it.
Go Tools>Options>Calculation
Tick the tickbox for "Iteration"
Set Maximum iterations to 1 and maximum change to 0.01

As I said though, I'm not sure what effect this would have on any other calculations in the workbook

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[luceze]

Or in the worksheet on change event you could try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If [c1] > [b1] Then
[b1] = [c1]
End If
End Sub

Geoff,
Any thoughts?

Eric

 

[xlbo]

Eric - that is pretty much what I was going to post but then I realised I was in the MSOffice forum and code hadn't been asked for initially. That would work fine except you would need to turn off events while you update the cell or you get an infinite loop

If [c1] > [b1] Then
  application.enableevents = false
  [b1] = [c1]
  application.enableevents = true

Plus, I havn't really had a chance to test out using iterations for a while - many ways to skin an excel problem / cat ;-)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[altaratz]

Thanks to both of you so much!!!! - You really saved my ass.

Ethan