[Issue] Picture anchored to cell erratically moves

[giuvi]

It handles about an images added to a spreadsheet in AOO Calc 4.1.15 (OS: Linux, OpenSUSE 15.6).

I have a rather big spreadsheet, where I record my work activities in a year, sorted by day and time.
Direct after the end of every month I must report the work activities of the last month to my employer. For this I use filters to select the related records.

At the bottom of the monthly report there are some general information as some sums (work time, special allowances). In this "bottom area" there is also my signature, added as image and anchored to a cell in this area.
That means, when I filter the activities for a certain month, the the header and the bottom of report are both not hidden.

My Problem:
After filter selection as done, the picture with my signature moves to another position. Sometimes it slightly slips few centimetres, sometimes more rows over the original cell.

I did read many similar threads in this forum; i.e. Locking inserted images in place
viewtopic.php?f=9&t=29097#p298558

There Ken Johnson (2nd post) wrote:
"Calc cannot handle images. They will not move from there current cells when you do a sort."

and acknak (last post) wrote:
"Calc can anchor an image to a particular cell, but that's just a location relative to the spreadsheet grid. The image is not contained in the cell and it won't move with the cell content."

Well I see, AOO-Calc is not perfect and is no more maintained for many years, but in my case no cells are added or deleted; the image is anchored to a cell, which is never hidden, even if filters are used.

Does anybody know, how it is possible to let the image remain at the anchor cell, as Ken Johnson wrote?

giuvi

[MrProgrammer]

Does anybody know, how it is possible to let the image remain at the anchor cell

In 15 years all of the posts which I've seen about anchoring images to cells have come to the same conclusion: It is not possible to do that reliably in Calc. So the answer to your how is it possible question is You can't. You can use a database to store data and images or you can put your images in a directory on your computer and put hyperlinks to them in your spreadsheet cells.

[giuvi]

Hi MrProgrammer,

thanks for your advise!
Do you mean
Insert Picture -> From File ... in which the option "Link" is active?

[MrProgrammer]

Do you mean: Insert Picture -> From File ... in which the option "Link" is active?

No. That would still display an image in your spreadsheet. As I understand the situation, anchoring an image to a cell sometimes causes a display problem. The image appears, but in the wrong location on the screen. People find that this feature is unreliable. The hyperlink technique puts text in the cell, not an image. You control what the text says. When you hover on the text the mouse pointer changes to a hand. Clicking the text will open the image in your computer's image application, not in Calc. If you want OpenOffice to display the image you must use a database, not a spreadsheet. I do not offer to assist with a database solution but you can find topics about it.

I meant use either:
• Insert → Hyperlink → Document → Path → Give address of file or use Open File button to browse → Text → Your choice → Apply → Close → Enter
• The function =HYPERLINK(path;text)

The first method is a dialog. The second method uses a function in the cell.

For the first method be sure to press Enter after Close. Browsing with the Open File button will give you the correct syntax for the path. You must use the correct syntax for the file name in either method. For the HYPERLINK function you will use the same syntax, but because the function requires a character string you will put the file name in double quotes. By default the text which the function creates looks like any other cell text. But you can apply a style to the cell to control the text's appearance. For example you can have it underlined with a blue font. This is sample syntax for the function using a picture on my computer:
=HYPERLINK("file:///Users/Guest/Desktop/Cranberries%20and%20Guava.JPG";"Click me")

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[giuvi]

Do you mean: Insert Picture -> From File ... in which the option "Link" is active?

(...) The hyperlink technique puts text in the cell, not an image. You control what the text says. When you hover on the text the mouse pointer changes to a hand. Clicking the text will open the image in your computer's image application, not in Calc. If you want OpenOffice to display the image you must use a database, not a spreadsheet. (...)

Sorry for my late reaction ...
I've already used many times your suggestion in other situations, but now it can't solve my issue, because the object to be load into the spreadsheet is an image of my signature and because I have to sent the monthly work time reports as PDF (a little part of the entire spreadsheet) via email to the employer and it neither has nor should have free access to my signature image file.

Question: it is possible to use a macro for this, where position and size parameters are predefined in the macro?

[Hagar Delest]

the object to be load into the spreadsheet is an image of my signature and because I have to sent the monthly work time reports as PDF (a little part of the entire spreadsheet) via email to the employer and it neither has nor should have free access to my signature image file.

If it is a standard PDF, then anyone can right click the image and retrieve it. Even if you add security options, screenshots can be made.
Thus, the signature is just for fun. If you want something stronger, then use a digital signature service.

Note that PDF tools can also add a kind of stamp made of a pic. For a professional look, use a svg graphic and avoid png or jpg that will show the pixels (and sometimes shades of gray when the signature has been scanned from a paper).

[giuvi]

Hi!
I've thought this critic point already on writing the post!
Yes, it is possible. And it is also possible to scan one's signature on paper and to misuse it.
Solution: no more sign.

[Hagar Delest]

OK, it was just to make sure.
Can you upload a sample file with dummy figures (see How to attach a document here)?

I tried with few data and filtering some rows moves the pic at the bottom upward, as expected (with LibreOffice). Thus, I don't really see the problem.

As a workaround, you can try the pic embedded as the background of the footer. But not that convenient.

[giuvi]

Hi!
attached you see a dummy spreadsheet with some notes on the top cells.

STRANGE: after created this calc file I noticed, the image added instead of signature, on the bottom of the report, near to subtotal does not move selecting cells (filtering) as experienced in original files in the last month.
Possible reason: not big file now?

ONLY FOR INFORMATION:
I use AOO not LO. I have explained my reasons already many times in other threads in this forum. Please, don't ask me again to change.

[Hagar Delest]

Here is what I see:

Sign_10.png (43.53 KiB) Viewed 285 times

Sign_11.png (29.42 KiB) Viewed 285 times

[giuvi]

YES, exactly what I see using the test file, but not the really used spreadsheet (= 1,82 MB).
So I really think, the issue occurs because the big file. In the real calc file only the tab, that you have seen contains more than 800 rows, 41 columns; the most with formulas; and about 20 cells text comments. Therefore there are many other tabs: some of them contains the background or complement data for the monthly report, the others are elaborations for checking data, payslip and similar.
BTW: The Graphic cache is set to 256 MB.

[giuvi]

The real spreadsheet
without filtering:

Screenshot_01.png (37.47 KiB) Viewed 273 times

filtered:

Screenshot_02.png (93.08 KiB) Viewed 273 times

[Hagar Delest]

I'm wondering if the problem is not the anchor of the graphic. It may be anchored to a line that is filtered out, thus the text below moves up when the graphic doesn't.
If you move the graphic downward so that the anchor is in the RZ-Stunden cell, does it make a difference?

[giuvi]

It were a possibility, but it was already anchored to the RZ cell:

Screenshot_03.png (23.6 KiB) Viewed 258 times

Don't you think, it could depend on the file size?

[Hagar Delest]

Well, I don't think so but who knows.
Just in case, if you anchor the graphic to a cell on the line of Gesamt, any improvement?
Does it happen only when the extra text is missing: (*) davon...

[giuvi]

Just in case, if you anchor the graphic to a cell on the line of Gesamt, any improvement?

In order to get anchoring to the a cell in row of Gesamt, I had to mark a cell, that usually disappears after filtering (because the last one on the report before subtotal). Here:

Screenshot_04.png (25.13 KiB) Viewed 254 times

After the picture is moved in the right position, also the anchor moves:

Screenshot_04a.png (27.01 KiB) Viewed 254 times

But no, no improvements.

Does it happen only when the extra text is missing: (*) davon...

The line with (*) davon ... does never hides through filtering. The cell I811 simply contains a nearly complex formula (IF + CONCATENATE). When the values of night and holidays work time = 0, the result is "", so in this case you don't see anything there.

With filter on and in case with (*) davon ...

Screenshot_04b.png (36.17 KiB) Viewed 254 times

Now I came back to my previous question:
What about a macro, that downloads the picture from directory and set it in position and size parameters, predefined in the macro?

[Alex1]

In OpenOffice, if I select a value using the filter in any column, the image moves to the wrong place, and stays there if I select All.
No problems in LibreOffice, though I had to turn the filter off and on before it worked. If the filter hides row 779, the image is invisible.

[Hagar Delest]

In order to get anchoring to the a cell in row of Gesamt, I had to mark a cell, that usually disappears after filtering (because the last one on the report before subtotal). Here:

Screenshot_04.png

After the picture is moved in the right position, also the anchor moves

I meant to keep the pic anchored to the line with Gesamt, just to see if it works better. If so, that would tell that the problem is with the row where the pic is anchored.
BTW, is there a combination of your test file that shows the problem?
Because I couldn't reproduce it (except by hiding row 779 indeed).

[giuvi]

Hagar Deles wrote:

I meant to keep the pic anchored to the line with Gesamt, just to see if it works better. If so, that would tell that the problem is with the row where the pic is anchored.

In fact at last attempt the pic was anchored to the line with "Gesamt" (see screenshot_04.png on post Fri Jun 13, 2025 9:41 pm). And as already shown the result is not good (see screenshot_04b.png on post Fri Jun 13, 2025 9:41 pm)

Hagar Deles wrote:

BTW, is there a combination of your test file that shows the problem?
Because I couldn't reproduce it (except by hiding row 779 indeed)

No. As I have already written, with the test file I cannot reproduce the issue too. That's why I suspect a combination with the spreadsheet complexity.
"... except hiding row 779 indeed": I would say "obviously!" But the either the lines 777 til 782 nor any cell in the same lines are hidden or deleted, when I produce the monthly work report, as they belongs to "report foot"! So, I think, there is no need to explore with these lines.

[Hagar Delest]

I don't understand your screenshot 04 because with LO Calc, the anchor is in a top left corner of a cell,aligned with the top border of the pic. I can't have the anchor at the middle (height) of the pic. Maybe something specific to AOO Calc.
Well, quite at a loss here.
If you blank all the cells containing data, can you share the original file? Keeping the data that can filter the rows.

[cwolan]

Maybe Issue 120506
(Flying object(the anchor is “to cell”) move to wrong position when hide some rows by filter or collapse).