Page 1 of 1

[Solved] Refer to a variable named expression

PostPosted: Wed Dec 04, 2019 6:44 pm
by rikus
Example.ods
(15.83 KiB) Downloaded 10 times


Hi,
I need a formula which refers, depending on the contents of a certain cell, to another named expression, see attached example.

The named expressions are created using top row and left column (In the example from the cells B2:E6).
The function INDIRECT does not work with named expressions (example 1). It only works if you know the exact cell reference (example 2).

Any idea how to solve this problem?

Txs,
Rikus

Mr. Programmer thank you for the very quick answer.

Re: refer to a variable named expression

PostPosted: Wed Dec 04, 2019 7:28 pm
by MrProgrammer
Hi, and welcome to the forum.

rikus wrote:I need a formula which refers, depending on the contents of a certain cell, to another named expression, see attached example.
For your first example use =INDIRECT(B9)!INDIRECT(B10) not =INDIRECT(CONCATENATE($B$9;"!";$B$10)).

The INDIRECT() function requires a text expression which specifies a cell range, say "B3" or "B3:B6", or you can pass a text expression which evaluates to a defined name that specifies a cell range. In cell B15 you have passed "'Row1!Column1" to INDIRECT which is not a cell range nor a defined name but an expression you want evaluated. No function in Calc can evaluate expressions.

In my formula, =INDIRECT(B9)!INDIRECT(B10) is =INDIRECT("Row1")!INDIRECT("Column1") which is =$Sheet1.$B$3:$E$3!$Sheet1.$B$3:$B$6 which is =$Sheet1.$B$3,

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know