Apache OpenOffice Community Forum

Hi everyone!
I searched for this request but I couldn't find anyone asking this same question.
To avoid Error 512 (too long formulas) I decided to split the formulas in my spreadsheet between multiple cells, in a "tree-shaped" form.
Example:

instead of
A1 =SUM(IF(D2=1;E2;0);IF(D3=1;E3;0))

I used
A1 =SUM(B1;C1)
B1 =IF(D2=1;E2;0)
C1 =IF(D3=1;E3;0)

This is just a single-step, it is much more complex than this but the example gives the idea.
My question is: can I reverse this process in a quicker way than editing all the formulas manually? I realised that a lot of "chains" of formulas can be shortened (or short-cut, meaning using less cells with longer formulas), so I would like to substitute the cell references in the final formulas with the formulas contained in the cells they refer to. To follow the same example, it would be:

from:
A1 =SUM(B1;C1)
B1 =IF(D2=1;E2;0)
C1 =IF(D3=1;E3;0)

to:
A1 =SUM(IF(D2=1;E2;0);IF(D3=1;E3;0))

I doubt that there is an internal OpenOffice feature which would do this, but before putting myself into a world of pain editing hundreds of formulas I thought I'd give it a try =)

Thank you in advance and regardless from your answers, also for all the times you have helped me on this forums!
I wish a nice sunday to everyone =)

Please upload your ODF type sample file here.

Acidello wrote:I would like to substitute the cell references in the final formulas with the formulas contained in the cells they refer to

I don't recommend that you make these substitutions. Long formulas are hard to understand, difficult to debug when they don't work, and are sometimes inefficient.

Acidello wrote:I realised that a lot of "chains" of formulas can be shortened (or short-cut, meaning using less cells with longer formulas)

Depending on how the formulas are structured, you may not be able use these simplifications when you create a monster formula, thus leading to Err:512 again. If what you have is working, you risk breaking it again.

[Tutorial] Ten concepts that every Calc user should know

Zizi64 wrote:Please upload your ODF type sample file here.

Sure! Thanks for the attention
It is a calculator for a strategy / city building game:

https://drive.google.com/file/d/1l4K_wg ... sp=sharing

Edit: 2026-02-08: Broken link -- MrProgrammer, forum moderator

To stay in topic, for instance, as soon as you open the file the selection will be on column AO. Those cells AO173, AO175 and AO176 appear in formulas on the same row on column AK, I would like (among other things) to have those cells in column AK (AK173:AK177) referring to " 'Profile & Difficulty Settings'.$H$113 " (which is the content of all cells on column AO173) in place of "AO173".

This could be done for all similar cells on column AK, as well as many other cases on different places in the spreadsheet, which as you can see is quite large.

MrProgrammer wrote:I don't recommend that you make these substitutions. Long formulas are hard to understand, difficult to debug when they don't work, and are sometimes inefficient.

Thanks for your answer! Do you mean that the program could even take less time to compute a large amount of shorter formulas, than a smaller amount of longer formulas?
However, in many cases it would be so simple as to write SUM(IF();IF();IF()) instead of SUM(A1;B1;C1) with those IF() in the separate cells. I insist because getting rid of some cells would allow me to have a much clearer view of what I am doing and actually help the debug.

MrProgrammer wrote:Depending on how the formulas are structured, you may not be able use these simplifications when you create a monster formula, thus leading to Err:512 again. If what you have is working, you risk breaking it again.

I understand this, but I am so 100% sure that in most cases the final formulas wouldn't be too long, because I basically chose a line of action for all the spreadsheet, due to a few specific cases in which formulas were too long, but I found it easier to write down the code if I visually divided stuff. Now that I know how things work and I am more familiar with the spreadsheet (after a few months I am working on it) I would rather have a more compact view than a detailed but space consuming sheet.

MrProgrammer wrote:[Tutorial] Ten concepts that every Calc user should know

I read all this but I didn't find a feature or mention of this topic

In my opinion, the structure of your sample file is absolutely unsuitable for automatize the task. Somewhere there are merged rows and there are unmerged rows; there are are empty and non empty rows.
In other words: It is very difficolut to use a macro or some other tool (for example the Find and Replace) on a non-continous, non consequent Cell range.

I see, but if I got rid of the merged rows I presume it would be somehow possible? Is the "Find and Replace" tool enough? Or, are there macros already created for this kind of tasks or shall I make one myself?
Thanks for the answer!

It seems quasi same quantity of working to replace the merged rows (and the empty rows after unmerge the merged rows) manually, than modifying the formulas manually.

Zizi64 wrote:It seems quasi same quantity of working to replace the merged rows (and the empty rows after unmerge the merged rows) manually, than modifying the formulas manually.

Yes actually I was realising the same thing
Thank you for your help!
Topic markes as solved.