I have in C11 some text in a table. I want in C20 a text saying “the content is in C11”. But also, I want automatic filters to keep working. Let’s say I sort the column, so C11 goes now as C8 and C20 keeps the position. I want it to say “the content is in C8”. How can I do that? I tried with CELL("ADRESS";C11) but it’s not what I want.
Cross-posted from: https://old.reddit.com/r/libreoffice/comments/wute2w/how_to_get_the_cell_coordinates_even_if_its_moved/.
1 Like
The cells not move. Only the cell content will move.
The cell contents never refers to the cells where they located. (Except the results of some functions like the ROW() and COLUMN().)
But you can Search the unique values in a column by some cell funstions. The result will be a relative position in the searched cell range.
1 Like
hmm what a pitty, because I might change its content 
You can use =CELL("address";C11) if you enable Update references when sorting range of cells under Tools → Options → Calc → General, Input Settings. However, that may get in the way if references to cells within the to be sorted range should stay relative to the sorted rows. Both together is not possible. YMMV.
Very few users may fully understand the implications of the mentioned option. I probably also don’t.
On the other hand we can mark and identify the few VeryImportantCells we want to track the position of the probably moved content for by a searchable format property. Since default sorting with option Include Formats enabled also moves the CellStyle, we can (e.g.) use a suffix to some CellStyle names to find “moved cells” with a macro looking for the styles.
The attached document can demonstrate the details. For easy passing of a range rtesztricting the search I use Option VBAsupport 1 in the example.
disask80939VeryImportantCells.ods (17.4 KB)
Used for sheets with lots of funny formatting, the UDF I wrote will not be very efficient, however.
1 Like