What I want is define a variable, that is holding a formula, and when I use that =variable somewhere, to get the references in the formula related to where it is used, not to where it was created.
Excuse me if a explain myself badly, english is not my mother language. I need help to figure things out.
I have 2 sheets, “node”, where actual data is, and “variables” where i define variables, so they don’t get in the way.
I have a repetitive operation, and I want to store the formula in a variable (or should I say a named cell), so I have to do changes only in one place and then just refer to =variable on each cell. Pretty common use case.
My formula is:
=CONCAT($node.$A23;“20 “;(COUNTIFS($node.$A:$node.$A;CONCAT($node.$A23;”_*”)))*vlan_to_vlan_y;" box";CHAR(10);“AICONFILLCOLOR 240 240 240”;CHAR(10);“AICONOUTLINECOLOR 3 0 255”)
My problem is the formula’s cell references are evaluated based on =variable original position (the cell variables.A23 where named variable “variable” is defined and the formula stored), regardless if I’m using =variable on node.P23 or node.P60, it will allways refer and do the calculations using values stored in node.A23 (so the absolute positions $node.$A contained in the formula is working as expected, but the 23 is acting as if it was $23 wich it is not).
If I just copy&paste the contents of variables.A23 anywhere in node sheet (except column A where it’s not expected to be, as it would contain a reference to itself) it works right and produces results like:
20 42 box AICONFILLCOLOR 240 240 240 AICONOUTLINECOLOR 3 0 255
vlan_to_vlan_y is another variable, that contains a number (of pixels). Not related to my question, but I’m using this to produce network weathermap configuration files from data stored in an xlsx file (from python using xlrd).
I’ve tried using INDIRECT(“variable”) and INDIRECT(“variables.A23”) to no avail and some variations with and without absolute references (to the columns) in the formula. cell references contained in the formula are always evaluated considering cell variables.A23 as the “origin of coordinates”.
Edit: I’ve added an example odf file (example.odf). And will try to explain myself better…
Edit2: corrected case and explanations, were swapped, and some formatting.
Cell variables.B3 contains “the formula”, that cell is named partition_icon.
- On node.B1 I’ve used =partition_icon, the result is “20 0 box…”
- On node.B6 I’ve copied B3 and pasted it into it, the result is “20 63 box…”
I’d like Case 2 to behave as Case 1. That is… using =variable that contains a formula get the same results as copying and pasting said formula.
Case 1 does what it does because it is using $node.A3 content for the CONCAT, wich results in node_1_1_. As node_1_1_ matches nothing (in the COUNTIFS)…0*vlan_to_vlan_y = 0.
Case 2 instead as it is pasted on row 6 uses $node.A6 content for the CONCAT, wich results in "node_2_ "wich matches 3 cells and so result number is 3 * vlan_to_vlan_y (wich is 21) = 63. This is the desired result.
Thank you very much.example.ods