Formula result not updating until hard recalc

[redart]

Hi,
I have the formula “=INDEX(AY:AY,H$1+2)”, where H$1 contains the row number where the formula sits. When I type a new value into the referenced cell the formula result doesn’t update, despite autocalc being on. Using F9 to recalculate doesn’t work either, but Shift-Ctrl-F9 (hard recalculate) does. If I copy and paste the formula back into the cell it starts working as expected. Each day the formula gets copied to the next row down via macro, and cell H$1 gets updated with the new row number. I suspect this is where the problem lies but can’t figure out exactly where and how. Any suggestions on what’s going on?. Thanks.

7.3.7.2 / LibreOffice Community
Build ID: 30(Build:2)
Linux 5.15

[karolus]

@redart

Each day the formula gets copied to the next row down via macro,

show the macro-code!

[redart]

lastrow = sheet.getcellrangebyname("H1").value											'Get current last row of data
Rows.insertByIndex(lastrow,1)															'Insert new blank row after lastrow

src = Sheet.getCellRangeByPosition(0, lastrow-1, 75, lastrow-1).RangeAddress			'Set copy range (src) = last line of data
trg = Sheet.getCellByposition(0,lastrow).CellAddress									'Set paste destination (trg) as first cell in new row
Sheet.Copyrange(trg, src)																'Copy src to trg

Range = Sheet.getCellRangeByPosition(0, lastrow-1, 75, lastrow-1)						'Copy yesterday's row to values only
out = Range.getDataArray()						
Range.setDataArray(out) 

[JohnSUN]

@redart

and cell H$1 gets updated with the new row number

Sorry, I don’t see in which line of the given code snippet this happens.

@redart

but Shift-Ctrl-F9 (hard recalculate) does.

You can do it from macro with ThisComponent.calculateAll()

[redart]

Thanks for the suggestion @JohnSUN

@JohnSUN
Sorry, I don’t see in which line of the given code snippet this happens.

My bad…H1 has count formula to give total number of rows so increments each day