## [Solved] Refer to a variable named expression

Discuss the spreadsheet application

### [Solved] Refer to a variable named expression

Example.ods

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.
Last edited by rikus on Wed Dec 04, 2019 10:56 pm, edited 1 time in total.
LibreOffice 4.3.3.2 Linux debian
rikus

Posts: 1
Joined: Sat Dec 12, 2015 7:17 pm

### Re: refer to a variable named expression

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
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Moderator

Posts: 3951
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA