[Solved] Syntax problem

Discuss the spreadsheet application

[Solved] Syntax problem

Postby Ilan » Wed May 21, 2008 12:46 pm

I have 12 sheets, one for each month, labeled 1 to 12.
On a master sheet I have a number 1 to 12 which determines which sheet I want to use.

I want the maximum in column a, so I use MAX('1'.A3:A103) and this works fine for sheet 1.
But now I want it to work according to the value of the cell so I use
ADDRESS(3;1;4;INDIRECT(B2))
which returns me 1.A3 which is almost like '1'.A3.
The trouble is the "almost" and if I use the ADDRESS function it will give me an error.

What is the proper way to make my MAX function pick up the proper sheet?

Thanks,
Ilan
Last edited by Ilan on Wed May 21, 2008 2:41 pm, edited 1 time in total.
OOo 3.0.X on Ubuntu 8.x + Windows XP
Ilan
 
Posts: 61
Joined: Tue Apr 15, 2008 9:44 am
Location: Haifa, Israel

Re: syntax problem

Postby Villeroy » Wed May 21, 2008 1:35 pm

If '1' works as a sheet name then you should try:
Code: Select all   Expand viewCollapse view
ADDRESS(3;1;4;"'"&INDIRECT(B2)&"'")

concatenating (&) the result of INDIRECT with single quotes. Each quote in doublequotes like any other literal string.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28544
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: syntax problem

Postby Ilan » Wed May 21, 2008 2:35 pm

No, it doesn't work. I made my own function which outputs a string which I put into B8
I see the value in B8 is '1'.C3:C103
In B5 I have 2.
B9 =INDEX(B8;B5) and it gives me Err:504
If I use =INDEX('1'.C3:C103;B5) everything works fine.

Apparently it isn't parsing correctly (or I don't know what the format is to make it parse correctly).
In any case a sheet name of '1' is no problem whatsoever.
OOo 3.0.X on Ubuntu 8.x + Windows XP
Ilan
 
Posts: 61
Joined: Tue Apr 15, 2008 9:44 am
Location: Haifa, Israel

Re: syntax problem

Postby Ilan » Wed May 21, 2008 2:40 pm

Got it! I need
=INDEX(indirect(b8);b5)

Thanks,
Ilan
OOo 3.0.X on Ubuntu 8.x + Windows XP
Ilan
 
Posts: 61
Joined: Tue Apr 15, 2008 9:44 am
Location: Haifa, Israel

Re: syntax problem

Postby Villeroy » Wed May 21, 2008 2:41 pm

I created a sheet named "1" and put an "X" into cell A1.
On another sheet I tried several references in A1:A6:
Code: Select all   Expand viewCollapse view
X   ='1'.A1
'1'.$A$1   =ADDRESS(1;1;1;"'1'")
X   =INDIRECT(A2)
1   
'1'.$A$1   =ADDRESS(1;1;1;"'"&A4&"'")
X   =INDIRECT(A5)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28544
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: syntax problem

Postby Villeroy » Wed May 21, 2008 2:46 pm

Ilan wrote:Got it! I need
=INDEX(indirect(b8);b5)

Thanks,
Ilan

I would prefer OFFSET over INDEX because it is more flexible and compatible to Excel ("!" vs. ".").
OFFSET(A1;1;2;3;4) --> From cell A1 one row down, 2 columns to the right, resized to 3 rows and 4 columns --> reference C2:F4
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28544
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: Bill and 20 guests