Support Board
Date/Time: Sat, 14 Feb 2026 23:39:44 +0000
[Programming Help] - Spreadsheet latch cell
View Count: 204
| [2025-12-29 00:48:58] |
| TraderGD - Posts: 13 |
|
Hello, I have an indicator that fires once and then gives no signal until the next occurence. I want to create a cell that remembers that change and stays "TRUE" until some other condition resets it. I have tried the information given here, Spreadsheet Studies Special Tasks: Locking the State of a One Time Condition I already had some formulas in a spreadhseet, so I used different columns than what the example listed; Sierra example "K3" is in Column T Sierra example "H3" is in column U Sierra example "E3" is in column V I have transposed all column labels in all formulas as above. After I enter the final command as listed in the example, I can sit here and watch Column U continuosly change from 1s to 0s in every visible row. Is that expected, and if so, I am a bit lost on which cell becomes my coondition-TRUE trigger. :) The concept behind it is simple; If an indicator has a crossover, then I want the condition to become TRUE and remain TRUE until it is RESET by either a) a trade entry ( which will be at a later time via a different trade entry condition trigger), or b) until a different indicator returns TRUE (which would indicate that the TRUE state of the original latch cell was now FALSE). See attached screenshot with the contents of cell "H3" displayed. |
 Sierra screenshot.JPG / V - Attached On 2025-12-29 00:48:36 UTC - Size: 278.05 KB - 43 views |
| [2025-12-29 01:52:47] |
| Sierra_Chart Engineering - Posts: 22856 |
|
The functionality as explained here does work as documented: Spreadsheet Studies Special Tasks: Locking the State of a One Time Condition The formula in U3 is not correct. It needs to reference itself. It is not referencing itself. The second parameter of the OR function needs to reference U3. Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing Date Time Of Last Edit: 2025-12-29 01:53:03
|
| [2025-12-29 13:42:59] |
| Sawtooth - Posts: 4313 |
|
I want the condition to become TRUE and remain TRUE until it is RESET
Here's an example of how to automate the reset:https://www.sawtoothtrade.com/example-9 |
| [2025-12-29 15:06:19] |
| TraderGD - Posts: 13 |
|
I saw that already, but that formula has all entries in a single column. I was assuming the data would need to go in three different columns, not three different rows? That is what sent me back to Sierra's instructions. =AND(OR(H3=TRUE,H4>1),H5) Date Time Of Last Edit: 2025-12-29 15:07:11
|
| [2025-12-29 16:36:10] |
| Sawtooth - Posts: 4313 |
|
I was assuming the data would need to go in three different columns, not three different rows?
Locking a cell TRUE doesn't require the use of a Formula Column, since Formula Columns auto-populate across all bars.The idea is you would reference $H$3 in your K3/M3 formulas. Note: It can be difficult to get the H5 formula to go back to TRUE from FALSE because it requires a secondary trigger. An easier way to do what you want is by creating a persistent TRUE variable in a Formula Column, using two IF statements, like this in W3: =IF(IndicatorCrossover,1,IF(OR(TradeEntry,2ndIndicatorTRUE),0,W4)) The first IF creates the persistent TRUE, the second IF releases it. Date Time Of Last Edit: 2025-12-29 16:38:04
|
| [2025-12-29 20:59:43] |
| TraderGD - Posts: 13 |
|
There are three cell references in the Sierra Chart example above. I have transposed those cells as follows; Sierra Chart K3 = My spreadsheet's T3 Sierra Chart H3 = My spreadsheet's U3 Sierra Chart E3 = My spreadsheet's V3 There are two formulas in the Sierra Chart Example which I transposed to the columns I am using in my spreadsheet; Formula #1 Sierra's version =OR(E3=10,K3>0) My version =OR(V3=10,T3>0) Formula #2 Sierra version =AND(OR(E3=10,K3>0),H3=FALSE) My version =AND(OR(V3=10,T3>0),U3=FALSE) There is one change that I made in my formula before I put it into my spreadsheet. Instead of using the number “10”, I am using the number “1”. Therefore, the actual formulas in my spreadsheet are as follows: Formula #1 Sierra's version =OR(E3=10,K3>0) My version =OR(V3=1,T3>0) Formula #2 Sierra version =AND(OR(E3=10,K3>0),H3=FALSE) My version =AND(OR(V3=1,T3>0),U3=FALSE) In cell V3 I have entered “=1”. The column U in my spreasheet continuously turns from 1 to 0 to 1 to 0, over and over. I have attached a PDF with colors showing the cell transposition, and a video that shows the cells going back and forth from 1 to 0. Date Time Of Last Edit: 2025-12-29 21:04:34
|
 Rev1 Latch Cell Issue.pdf - Attached On 2025-12-29 20:58:36 UTC - Size: 183.57 KB - 40 views Private File |
| [2025-12-29 21:51:12] |
| Sawtooth - Posts: 4313 |
|
I recommend using the persistent variable example in post #5.
|
| [2025-12-29 22:30:28] |
| TraderGD - Posts: 13 |
|
Sawtooth - "An easier way to do what you want is by creating a persistent TRUE variable in a Formula Column, using two IF statements, like this in W3: =IF(IndicatorCrossover,1,IF(OR(TradeEntry,2ndIndicatorTRUE),0,W4))" That is actually similar to how I started. When I could not get it to work as intended I went to the Sierra Help section. I have changed my spreadsheet so that Sierra's "E3" reference has been now replaced with my crossover trigger column, which in my spreadsheet is Column O. In this screenshot Sierra's "E3" is represented by my cell "O3". I have red arrows where there was a crossover event that produced a 1 in column O. Sierra's K3 does produce a 1 in that same row, but it is not staying as a 1. Attached is a screenshot of how Sierra's formulas are resulting. One of those two columns highlighted in green (presumed Sierra's K / my T) should stay as a "1" after the initial Crossover. Column U in my spreadsheet is assumed to be the latch reset condition. I have it exactly as Sierra's example, but with the cell names changed. =AND(OR(O3=1,T3>0),U3=FALSE) . But even if I delete all content in cell U3, it does not change anything in Column T. I will keep messing with it |
| columns.JPG / V - Attached On 2025-12-29 22:28:03 UTC - Size: 184.35 KB - 23 views |
| [2025-12-29 23:26:53] |
| Sawtooth - Posts: 4313 |
|
If you are going to use the lock TRUE method, test it manually in 3 cells in column H as in my example, not in Formula Columns. This will show you how it is supposed to work. As I said before, automatically resetting it can require trial and error. But first, get it to lock. I still think the persistent variable is most likely your best option. |
| [2025-12-29 23:39:01] |
| TraderGD - Posts: 13 |
|
Column H does something I did not realize. It does not auto-populate per bar. I will come at it again from that direction and see if I can get the result I am looking for.
|
| [2025-12-29 23:46:05] |
| Sawtooth - Posts: 4313 |
|
Yes, Column H is an open column for any use. I use it for user options, and reference it in formulas in Formula Columns. |
| [2025-12-30 15:35:18] |
| TraderGD - Posts: 13 |
|
Sawtooth, I built a very simple test spreadsheet in Excel to try the first formula you suggested. I am uploading a screenshot. I used H3-H5 as the first of the three tests, the other two tests are transposed but identical function. The result in cell "H3" seems to remain 0 regardless of the other results. |
| [2025-12-30 16:55:49] |
| Sawtooth - Posts: 4313 |
|
To test this example, do these steps: 1. Put this formula in H3: =AND(OR(H3=TRUE,H4>1),H5) 2. Put 1 in H4. 3. Put 1 in H5. 4. Put 2 in H4. H3 goes from 0 to 1. 5. Put 1 in H4. H3 stays 1 after the locking condition is removed. 6. Put 0 in H5. H3 goes to 0, lock is released. 7. Put 1 in H5. H3 stays 0, ready for the next lock. Note: This does not work in Excel. This only works in Sierra spreadsheets. |
To post a message in this thread, you need to log in with your Sierra Chart account: