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 =)
[Solved] mass-replace cell references with formulas
[Solved] mass-replace cell references with formulas
Last edited by robleyd on Sun Sep 12, 2021 10:35 am, edited 2 times in total.
Reason: Add green tick
Reason: Add green tick
Openoffice 4.1.13
Windows 10
Windows 10
Re: mass-replace cell reference with formula found in that c
Please upload your ODF type sample file here.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
- MrProgrammer
- Moderator
- Posts: 4907
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: mass-replace cell reference with formula found in that c
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 would like to substitute the cell references in the final formulas with the formulas contained in the cells they refer to
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.Acidello wrote:I realised that a lot of "chains" of formulas can be shortened (or short-cut, meaning using less cells with longer formulas)
[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: mass-replace cell reference with formula found in that c
Sure! Thanks for the attentionZizi64 wrote:Please upload your ODF type sample file here.
It is a calculator for a strategy / city building game:
https://drive.google.com/file/d/1l4K_wg ... sp=sharing
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.
Last edited by Acidello on Sat Sep 11, 2021 10:04 pm, edited 4 times in total.
Openoffice 4.1.13
Windows 10
Windows 10
Re: mass-replace cell reference with formula found in that c
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?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.
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.
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: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 read all this but I didn't find a feature or mention of this topicMrProgrammer wrote:[Tutorial] Ten concepts that every Calc user should know
Openoffice 4.1.13
Windows 10
Windows 10
Re: mass-replace cell reference with formula found in that c
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.
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.
Last edited by Zizi64 on Sun Sep 12, 2021 9:20 am, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: mass-replace cell reference with formula found in that c
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!
Thanks for the answer!
Openoffice 4.1.13
Windows 10
Windows 10
Re: mass-replace cell reference with formula found in that c
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.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: mass-replace cell reference with formula found in that c
Yes actually I was realising the same thingZizi64 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.
Thank you for your help!
Topic markes as solved.
Openoffice 4.1.13
Windows 10
Windows 10