Calc evaluate a formula contained in a cell with reference to where it's used (and not where "named cell"/variable is located)

[nicolatron2020]

Hello,

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.

1. On node.B1 I’ve used =partition_icon, the result is “20 0 box…”
2. 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

[LeroyG]

@nicolatron2020,

What is the content of node.A23?

Seems to me that a semicolon ( is missing before “vlan_to_vlan_y;”.

Created a file weathermap.ods, trying to understand.

[LeroyG]

@nicolatron2020,

In your file B6 content is:

   =CONCAT("20 ";(COUNTIFS($node.$A:$node.$A;CONCAT($node.$A6;"_*")))*vlan_to_vlan_y;" box";CHAR(10);"AICONFILLCOLOR 240 240 240";CHAR(10);"AICONOUTLINECOLOR 3 0 255")

I understand that the content must be: =partition_icon

So, copy B2 and paste in B6.

[nicolatron2020]

You’re right, explanations were swapped, I’ve corrected them (and left odf file as it was).

[keme]

The $ prefix makes the reference absolute. If you omit that, copying the formula to a different cell will use the relative position for the references (also across sheets).

The ROW() and COLUMN() functions with empty parameter list will return the row and column numbers of the cell where the formula is.

The ADDRESS() function gives an address converted to text, e.g. to be handled (possibly after string manipulation to adjust some parts of the address) by INDIRECT() to convert to an actual cell address reference.

OFFSET() allows you to modify an address reference point and range size.

Example:

The formula =ADDRESS(ROW();COLUMN()) will return the absolute address of the cell where the formula itself is entered.

As far as I can see, you have explained how you tried (and failed) to accomplish your goal. You have not explained what that goal is (what you seek to accomplish). I am not able at this time to divine the intended goal from the given formulas. Without knowing the objective, it is difficult to provide more explicit advice. If the above does not lead you in the right direction, please add details to your question or in a comment. Help us to help you!

Don’t add info in the Add answer / Propose your solution box. This increases the “answer count”, which makes it less likely to attract helpers.

[erAck]

I haven’t fully dug into what you actually want to achieve, but most likely you are misunderstanding what you call “variables” here. You only defined names for absolute cell positions, here partition_icon to be a name for cell $variables.$A$3 and of course wherever you use partition_icon in a formula it will use the value calculated by the formula in cell $variables.$A$3.

What you likely want instead is to define a named expression that uses relative cell references relative to the position where it is used. So place your cell cursor on cell B1 and hit Ctrl+F3 to get the Manage Names dialog and redefine partition_icon to the formula expression

CONCAT("20 ";(COUNTIFS($node.$A:$node.$A;CONCAT($node.$A1;"_*")))*vlan_to_vlan_y;" box";CHAR(10);"AICONFILLCOLOR 240 240 240";CHAR(10);"AICONOUTLINECOLOR 3 0 255")

The important part here is that while defining the expression on cell B1 the reference $node.$A1 contains a relative row reference “on the same row” (here row 1). Once having modified the named expression such and closed the dialog with OK, using =partition_icon as formula anywhere will calculate the reference as intended. You’ll also see when invoking the dialog again on another cell, for example B6, it will display $node.$A6 in the formula expression.

[nicolatron2020]

Thank you very much (and to the others misguided by my initial wrong terminology assumptions). You’re right on all accounts.