I need to store a "Yes" or "No" in a cell when a condition is met, even if that condition later becomes false again.
If A1 = >0
B2 = True
B2 stays true even when A1 = 0 again.
Is this possible?
I need to store a "Yes" or "No" in a cell when a condition is met, even if that condition later becomes false again.
If A1 = >0
B2 = True
B2 stays true even when A1 = 0 again.
Is this possible?
Last edited by AliGW; 12-13-2023 at 03:44 AM. Reason: Thread moved to the VBA section.
Yes, but you'll need VBA to lock in the formula result. Shall I move the thread for you?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Yes, move thread. thx
Since this will require vba, I will try to add more detail. I am building a cryptocurrency portfolio in excel. I'm putting in my stop loss price (location below). I have auto-refreshed prices for coins in my workbook (location listed below). I need to match my stop price to the current price and trigger a Yes or No (location below) in a certain range that remains permanent until deleted.
Sheet name = Entries
Cell Range for Yes or No to display = O3:O5000
Cell Range for Criteria 1= J3:J5000 where this displays the exchange that I purchased the coin
Cell Range for Criteria 2 = K3:K5000 where this displays the coin I purchased on that exchange.
Cell Range for Criteria 3 = N3:N5000 where this displays the stop loss price that should trigger the Yes/No
Sheet name = Price Updates (this sheet is where the price updates are for the coins
Cell Range A2:A5000 and B2:B5000 has the exchanges and Coins respectively but it is listed like this:
Column A Column B
Coinbase Coin 1
empty cell Coin 2
empty cell Coin 3
empty cell Coin 4
empty cell Coin 5
Mexc Coin 1
Empty Cell
etc.
The empty cells just mean the exchange above them.
Range G2:G5000 has all the coin prices.
Workbook attached.
This is what I pulled from ChatGPT:
Private Sub Worksheet_Change(ByVal Target As Range) Dim entriesSheet As Worksheet Dim priceUpdatesSheet As Worksheet Dim entriesRange As Range Dim criteria1Range As Range Dim criteria2Range As Range Dim criteria3Range As Range Dim priceUpdatesRange As Range Dim i As Long ' Set the sheets and ranges Set entriesSheet = Sheets("Entries") Set priceUpdatesSheet = Sheets("Price Updates") Set entriesRange = entriesSheet.Range("O3:O5000") Set criteria1Range = entriesSheet.Range("J3:J5000") Set criteria2Range = entriesSheet.Range("K3:K5000") Set criteria3Range = entriesSheet.Range("N3:N5000") Set priceUpdatesRange = priceUpdatesSheet.Range("A2:G5000") ' Check if the change occurred in the price updates range If Not Intersect(Target, priceUpdatesRange) Is Nothing Then Application.EnableEvents = False ' Loop through each row in the entries sheet For i = 3 To 5000 ' Check if the criteria match If criteria1Range.Cells(i, 1).Value = priceUpdatesSheet.Cells(Target.Row, 1).Value And _ criteria2Range.Cells(i, 1).Value = priceUpdatesSheet.Cells(Target.Row, 2).Value Then ' Check if the current price is below the stop loss price If priceUpdatesSheet.Cells(Target.Row, 7).Value <= criteria3Range.Cells(i, 1).Value Then entriesRange.Cells(i, 1).Value = "Yes" Else entriesRange.Cells(i, 1).Value = "No" End If End If Next i Application.EnableEvents = True End If End Sub
Does it work or do you still require assistance? If not, please mark this as solved.
Chat GPT never works. I just shared it to help.
OK - when you share things, please add some comment to make it clear why. But if it doesn't work, it won't help.
Someone will help soon, I am sure.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks