Changing updating flow? Is it possible?
I was told that Calc updates a spreadsheet by working down each column from left to right. At the same time it updates dependencies.
Is there a way to change the flow by having a spreadsheet updated row at a time?
I put together a share portfolio spreadsheet, assuming each row would be updated before touching the next row. There is one row for each share. Because of the update via cols the time it takes to grab data from the website is extra long.
Hard to tell without a concrete example, please edit your question and attach a sample spreadsheet document. Might be that each cell in a row does its own query that could be better organized.
Thank you for asking me to upload the problem spreadsheet. There are reasons why on this occasion I have not done so. However, I decided it would be best to ask advice on principle. What I am doing is conceptually simple:
List shares and get their current prices via a function written specifically for it. Manipulate the prices to show profit, gains, yields, and spread. One row per share.
The spreadsheet currently works well but it is slow. Purely downloading the bid and offer prices takes about 75secs. Updating the file takes 7mins.
I am sure one of the reasons is that each time a value is changed it effects other values which keep getting changed as the spreadsheet is recalculating.
Formula cells and their dependencies are recalculated as needed and encountered. There is no specific order unless the entire document is recalculated hard (Shift+Ctrl+F9), in which case columns are calculated top down from left to right. That can’t be changed and usually is faster because all data and formula cells are organized and stored in columns.
I think I have a solution:
Have a separate sheet that pulls the values and does calculations in a single column per security. Change the existing portfolio spreadsheet so it gets its values for each share from the the second sheet via Horizontal lookup. This should prevent unnecessary repeat recalculations in the existing sheet due to Calc not being able to do its recalculations row by row.
Full update restricted to a block of cells.
A solution might be to force a full recalculate one row at time ie a block. Does calc have such a function?
As asked, please attach a sample file, without that it’s not possible to test what’s happening.
No, such functionality does not exist. But you could make one row’s formula cells depend on a specific “trigger cell” and change that to trigger a recalculation of the dependants. Again, without a sample this is all just guessing around.
And please do not use the Answer or Suggest a solution field for comments that are not an answer to the original question / solution to the problem, use Comment instead. Thanks.
Could you give an example of how that works?
How about you upload a sample document that we’re asking for since two days?
Anyway, you could add a dependency to a trigger cell to all formulas, but already that depends on whether the result should be numeric or text, so here only for numeric results in a row 3 where the trigger cell is A3 and any text/character can be input there except a number. Append +N(A3) to your formula expressions. Where the N() function converts any text to numeric 0.
But again, without an actual sample this again is just guessing around. And it does not prevent recalculations that need to happen because other dependencies change.
Thank you all, for your help. I am currently redrafting the spreadsheet so that there is one share per column. So far it has reduced the update time by 50%. I have yet to complete the checking.I