Target cell in forumla changes with update from external link

[firehawkv]

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

[schiavinatto]

If the line shifts downwards, the change is automatic.

[Wanderer]

@firehawkv

a way to prevent it?

As said above: It is an automatic service of Calc. A way around this can be using INDIRECT(). If the Address is “hidden” in a string, it will not be adapted.
https://wiki.documentfoundation.org/Documentation/Calc_Functions/INDIRECT

@firehawkv

another solution

I would try to avoid the problem. You substitute to -1/+1, then SUM, so the result will be the the difference of counting the values. Therefore you could use COUNTIF() instead, and have no need to substitute values first.

https://wiki.documentfoundation.org/Documentation/Calc_Functions/COUNTIF

@firehawkv

macro on the data as the update happens

Can be a Problem: You substitute 1->-1, 2->1. So you have to ensure the macro is not run a second time on already converted data, because the second run would convert the previously generated 1 to -1. Maybe consider converting only your 2 to -1, this would leave no room for interpretation.

[firehawkv]

I ended up solving the issue with a workaround, the final formula I used is =SUMPRODUCT((B4:C4=1)*-1)+SUMPRODUCT(((B4:C4=2)/1)), where B4 is the the data coming in that I care about (2 or 1) and C is simply a blank column. The target of B4:C4 does not change when new data comes in, but it does change if the target is only B4 (or B4:B5 for that matter). Why, I don’t know, but I’m happy with what I have, so I don’t really care either.