Support Board
Date/Time: Wed, 12 Feb 2025 10:01:26 +0000
Post From: Spreadsheet study: Direct A3 result alignment between two sheets
| [2020-10-13 13:03:21] |
| User185210 - Posts: 66 |
|
Thank you, FLOOR(A3,15/1440) is the first part of the solution. This example to illustrate my idea: on sheet#1 15min chart: ------------------------------------------ sheet#1 A3 14:15:00 returned value of O3 is: 4 A4 14:00:00 returned value of O3 is: 15 A5 13:45:00 returned value of O3 is: 27 A6 13:30:00 returned value of O3 is: 5 A7 13:15:00 returned value of O3 is: 11 ... -------------------------------------------- What I would like to have extracted on sheet#2 1TickRange chart is: sheet#2 --------------------- A3 14:18:55 O3=4 (returned value from SHEET1 O3) A4 14:16:28 O4=4 (returned value from SHEET1 O3) A5 14:15:22 O5=4 (returned value from SHEET1 O3) A6 14:08:21 O6=15 (returned value from SHEET1 O4) A4 14:04:37 O7=15 (returned value from SHEET1 O4) A7 14:00:52 O8=15 (returned value from SHEET1 O4) A8 13:58:22 O9=27 (returned value from SHEET1 O5) A9 13:47:12 O10=27 (returned value from SHEET1 O5) A10 13:45:55 O11=27 (returned value from SHEET1 O5) ... --------------------- with "FLOOR(A3,15/1440)",it improves the original formula = IF(FLOOR(A3,15/1440)=SHEET1!A$3,SHEET1!O$3, IF(FLOOR(A3,15/1440)=SHEET1!A$4,SHEET1!O$4, IF(FLOOR(A3,15/1440)=SHEET1!A$5,SHEET1!O$5, IF(FLOOR(A3,15/1440)=SHEET1!A$6,SHEET1!O$6, IF(FLOOR(A3,15/1440)=SHEET1!A$7,SHEET1!O$7,NOVALUE))))) but it still needs me to adjust it for each bar individualy and I would like to have an automatic formula that could return all the results from sheet#1 to sheet#2 and not only the ones for which I specificaly put "O$3,O$4,O$5,O$6"... Is there a way? thank you |