Column letter in formula changing by itself even with $

[ddcpi]

Hello everyone,

I have a LibreOffice file where i have many html extenral links separated on different sheets.

In the beginning i insterted some forumulas in each of those external link sheets to extract some values to my main sheet ( sheet 1 ). I put those cell somewhere further away where there is no data of course.

Was working but noticed later that on some sheets all the cells moved by theirselves to another row column…

I was ok maybe it was because when the sheet refresh from the updating it mess the cells too so i decide to make a second sheet just next to each of the html external links sheet and put my formulas there so it won’t interact with the html external sheets and thus move the cells by itself.

Well it worked, my cells didn’t move since then and stays at the samle place but… i noticed now that the column letter i assigned in my formulas change by theirselve, i’m kind of confused…

For exemple i assigned $E$150 and on some sheets they become $B$150 or $J$150

What can i do to solve this issue ? Any suggestions would be appreciated.

Thanks

[LeroyG]

If you insert rows or columns, the reference will acomodate to the new position. So if the original formula says $E$150 and you deleted 3 rows to the left of E, the formula now will say $B$150.

Absolute References/Absolute Addressing is usefull when you copy/move the formula to another cell.

See LibreOffice Help on Addresses and References, Absolute and Relative.

Tested with LibreOffice 6.3.6.2 (x86); OS: Windows 6.1.

---

Add Answer is reserved for solutions.

Press edit below your question if you want to add more information; also can comment an answer.

[anon73440385]

What can i do to solve this issue ?

Don’t insert or delete columns. Absolute addresses are not changed, if you copy cell content to another cell, but they are changed if you insert or delete columns: Just make a simple test: Put =$B$1 into cell A1 (and any value into cell B1). Now select column B and Sheet -> Insert Columns -> Columns Before - Now watch formula in cell A1 → it is =$C$1. And that is desired behavior: You want A1 to show the same thing, even if you add/delete a column.

[ddcpi]

Hello guys and thanks for the feedback.

Note that the sheets where i import the html data contains only the the data from the site and i’ve set a refresh page time as well.

So i don’t add or delete anything in those html sheet at all. I think what happend is that the site contain some tables, those can somedays not be available on the site but reappear sometime later. So when my sheet is actualising it may not found data on those columns and i think this is where something is getting messed up.

Could this be a possibility ?

[newbie-02]

hello @ddcpi,
had a similar issue shortly, look at - ‘ask’ thread about it - , assumption is that updating from external data inserts space around the old download if the new download is bigger than the old one, as a security measure to not to overpaste other work done around, - good idea!!! -
that may change positions and referencing inside that sheet as well as other sheets referencing to it,
@erAck has recommended something with ‘named cells’ or similar, it worked for me,
let me know if this solves your question, will make the comment an answer then,