Home >> Support Board

Support Board

Date/Time: Fri, 19 Apr 2024 22:30:17 +0000

How to Post a Message for Support

Error copying formula referencing another sheet

View Count: 567

[2021-01-08 02:31:40] #1 [Go To First Post]
ijtraderji - Posts: 6
I find this error when for example I am on Sheet1: Steps to Reproduce 1. Create a new spreadsheet. Make it a quote spreadsheet. 2. Add an extra sheet to it, I named it "Underlying". 3. Make it a quote spreadsheet too. 4. On the 2nd Underlying sheet, add a few tickers like IBM AMD BYND TSLA AAPL 5. On the first sheet, enter a VLOOKUP formula like this: =VLOOKUP("BYND",Underlying!B:O,4,FALSE) 6. The formula should work properly. 7. Now right click the cell with the VLOOKUP formula, and click copy formula. 8. Paste formula into a different cell. 9. The formula has been altered to: =VLOOKUP("BYND",Underlying!B:Underlying!O,4,FALSE) Editing the formula slightly (for example changing the ticker) creates a #SYNTAX error.

[2021-01-08 04:00:12] #2 [Go To First Post]
ijtraderji - Posts: 6
A related, but different issue: Say in a quote spreadsheet I have a formula like this: =IF($U3=AF$1,$S3,"") If I copy this formula to the next column, the formula correctly changes to: =IF($U3=AG$1,$S3,"") However, the copied formula continues to calculate using the value in AF1 (instead of AG1). Copying and pasting a value or otherwise putting a new value into AG1 causes it to recalculate correctly. Date Time Of Last Edit: 2021-01-08 04:01:53

[2021-01-11 21:59:37] #3 [Go To First Post]
Jeffrey - Posts: 2098
I find this error when for example I am on Sheet1: Steps to Reproduce 1. Create a new spreadsheet. Make it a quote spreadsheet. 2. Add an extra sheet to it, I named it "Underlying". 3. Make it a quote spreadsheet too. 4. On the 2nd Underlying sheet, add a few tickers like IBM AMD BYND TSLA AAPL 5. On the first sheet, enter a VLOOKUP formula like this: =VLOOKUP("BYND",Underlying!B:O,4,FALSE) 6. The formula should work properly. 7. Now right click the cell with the VLOOKUP formula, and click copy formula. 8. Paste formula into a different cell. 9. The formula has been altered to: =VLOOKUP("BYND",Underlying!B:Underlying!O,4,FALSE) Editing the formula slightly (for example changing the ticker) creates a #SYNTAX error Thank you for the detailed steps to reproduce this issue. This should be fixed in the next release. A related, but different issue: Say in a quote spreadsheet I have a formula like this: =IF($U3=AF$1,$S3,"") If I copy this formula to the next column, the formula correctly changes to: =IF($U3=AG$1,$S3,"") However, the copied formula continues to calculate using the value in AF1 (instead of AG1). Copying and pasting a value or otherwise putting a new value into AG1 causes it to recalculate correctly. I was unable to reproduce this issue. Everything appeared to work as expected when I tried it.

[2021-01-11 22:31:54] #4 [Go To First Post]
ijtraderji - Posts: 6
Try this way: 1. In cells F2-F6 enter (one item per cell) This That Another One Two 2. In Cells H1-L1 enter (one item per cell) This That Another One Two 3. In cells H2-H7 enter the number 10 4. In cells I2-I7 enter the number 20 5. In cells J2-J7 enter the number 30 6. In cells K2-K7 enter the number 40 7. In cells L2-L7 enter the number 50 8. In cell H8 enter the formula: =IF($F2=H$1,H$2,"") The value should be 10 9. Copy this formula to I8:L8. All other cells will be empty. 10. Copy formula for cells H8:L8. 11. Paste to H9:L9, I9 will show value 20. 11. Paste to H10:L10, now all values show blank, although J10 should be 30. 12. Change the value in J1 to "A" and then back to "Another". J10 formula now populates with correct value of 30. See screenshot


Screenshot_2021-01-11_16-29-19.png / V - Attached On 2021-01-11 22:30:54 UTC - Size: 22.08 KB - 163 views

[2021-01-12 00:31:18] #5 [Go To First Post]
Jeffrey - Posts: 2098
Thank you for the clarification. This issue should also be fixed in the next release.

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