Home >> Support Board

Support Board

Date/Time: Sat, 18 May 2024 05:12:04 +0000

How to Post a Message for Support

Post From: Excel error & GetCorrespondingMatch multiple criteria selection

[2016-07-13 16:03:04] Link To Thread - Link To Post [Go To First Post]
User025902 - Posts: 8
Hello, I encountered few problems I can't solve when building a Spreadsheet Study which would plot COT data to charts. 1) Do you have plans for implementing COT data into default Studies? This would remove all the problems at once and reading through forums I believe I am not the only one waiting for this important piece. 2) I am able to get it working using Excel and array functions (Ctrl+Shift+Enter for confirming them). Alas, whenever the study is recalculated SierraChart automatically rewrites the functions to their default state (plain Enter) so they stop working. Is there a solution? 3) I wanted to replicate my Excel formulas in New Spreadsheets hoping this would solve the issue. However SCH plots Date in YYYY-MM-DD format. The COT data from cftc.gov are either in YYMMDD or DD.MM.YYYY format. Now Excel doesn't have problem with this as the information is the same, only format changes. But Sierra recognizes only characters and can't connect those formats. Is there any workaround without manually changing the source date (it gets updated weekly so this would be very inconvenient)? 4) GetCorrespondingMatch function is showing #VALUE error when using different sheet in the same spreadsheet as a source value. Why? (Looking for text value, works within the same Sheet) 5) I need to get match based on 2 criteria (Market Name and Date. This match happens only once per day per market thus only one row is valid.). In Excel this is done by multiplicating both conditions in "search" parameter of MATCH function and setting "1" as lookup value: MATCH(1,(A1=C:C)*(B1=D:D),0) If match is found for both A1 and B1 on the same row C, resp. D, only then function gives the row number which is then used as row parameter for Index. By the way GetCorrespondingMatch works as it uses only one collumn for search I have hard time coming up with a solution. Any suggestions would be greatly appreciated. Thank you