[Solved] Refer to a variable named expression

Discuss the spreadsheet application

[Solved] Refer to a variable named expression

Postby rikus » Wed Dec 04, 2019 6:44 pm

Example.ods
(15.83 KiB) Downloaded 16 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.
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

Postby MrProgrammer » Wed Dec 04, 2019 7:28 pm

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).
User avatar
MrProgrammer
Moderator
 
Posts: 3951
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 32 guests