Login Page - Create Account

Support Board


Date/Time: Thu, 28 Mar 2024 19:45:22 +0000



[User Discussion] - Spread Sheet formula to establish if the sheet is presently being outputted to from chart

View Count: 1365

[2018-11-13 01:29:35]
enemyspy - Posts: 304
I have a spreadsheet study with multiple sheets for several charts at once. A static sheet uses all the sheets in the spreadsheet study to display certain data. I want to make a way to ignore data that comes from sheets in the study that are presently not connected to a chart.

Since a chart # that does not exist in the study creates a sheet, and the sheet stays put when you open the study in a new chart book whether linked to a chart or not:
It would be useful to establish if the sheet is linked to a chart. I thought this could possibly be done by checking for changes in one of the J cells when replaying or connected to data feed since the data does not change if not linked. I was unable to succeed at making a formula that can do that though.

Has anyone ever been able to make a formula to establish if a sheet is linked to a chart?
If so would you care to share?
Date Time Of Last Edit: 2018-11-13 01:31:19
[2018-11-13 03:18:18]
Sawtooth - Posts: 3952
By default, the Sheet# matches the Chart#. This is the case if 'Chart Data Output Sheet Number' is set to 0, and the 'Chart Data Output Sheet Name' is blank.

It used to be the case when a spreadsheet study is added to a chart# that all other respective Sheet#s in between would get a blank sheet not associated with a chart. This is no longer the case. Only Chart#s that have a spreadsheet study will get a respective Sheet#.

If you add an existing spreadsheet study via a study collection, or by selecting it by name, and you are adding it to a chart that wasn't used previously, you'll have sheets that are not associated with a chart. You can simple Delete them, once you've confirmed there is no respective chart#.

The best way to keep things organized is to Duplicate/Delete charts so that all of the chart#s are sequential with no gaps in numbering. Then all of the spreadsheet Sheet#s will also be sequential. And leave the 'Chart Data Output Sheet Name' blank. And if you are using one chart's sheet formulas for all of the other sheets, make it be Sheet1. Keep it simple and straightforward.

If you need a static sheet for certain data, just add a sheet and give it a unique name other than a Sheet#. It won't be associated with a chart.
[2018-11-13 08:42:34]
enemyspy - Posts: 304
Thank you. So what happens is the study is used in a number of different ways. Sometimes it is used on 1 chart and other times it is used on 30 charts. No issues with creating the static sheets and naming them. No issues with adjusting for gaps in chart sequence either. Not really wanting to delete up to 50 sheets manually every time I re open a chart book though.

The best solution I have right now is that the bid and ask are usually 0 in the j columns of sheets that are not associated with a chart. This eliminates most of the inapplicable data from my static sheets But not always.

Can you think of a way to identify true of false if the data in a sheet is changing (connected to a chart)?
Date Time Of Last Edit: 2018-11-13 08:45:41
[2018-11-13 18:20:16]
Sawtooth - Posts: 3952
Can you think of a way to identify true or false if the data in a sheet is changing (connected to a chart)?
Try this:
=$J$41<=A3 for chart updating = FALSE
or
=$J$41>=A3 for chart updating = TRUE

In a chart that is updating, J41 will be greater than A3.
[2018-11-13 20:07:16]
enemyspy - Posts: 304
Nice that is perefect. Thanks Tom.

To post a message in this thread, you need to log in with your Sierra Chart account:

Login

Login Page - Create Account