I’m using an external link to a text document that is being created by a serial data device. It automatically updates periodically. My goal is to have a graph that updates automatically as new data comes in.
The data comes in as 1 or 2, I want it to be -1 or +1, so I use =IF(B1=1,-1,IF(B1=2,1,0)) and convert it. Then I sum the data. No issues with this part. I want to have the formula to run automatically each time the form is updated with new data. It works if I type in the data manually, but for some reason the target cell changes when new data comes it.
Let’s say B1 to B3 have data and B4 is the cell that will be filled with the next update. I try targeting the next empty cell empty cell, say B4, =IF(B4=1,-1,IF(B4=2,1,0)), before it’s filled, with the intention of the formula automatically running once B4 gets filled. But when B4 gets filled on update, I see the formula has changed to target B5 (so it now reads =IF(B5=1,-1,IF(B5=2,1,0)), and when B5 gets filled, the formula targets B6 and so on, always targeting the next empty cell rather than the original target after that got filled with new data.
Is there a reason this is happening and/or a way to prevent it? Or another solution to the problem (maybe running a macro on the data as the update happens)?
LibreOffice 24.2.7.2(X86_64) on Linux Mint