diff options
authorSteve Fanning <>2020-05-29 17:38:14 +0200
committerOlivier Hallot <>2020-06-08 18:18:49 +0200
commit4b5401b421e6a6d42e0fa594db3948aa89586a5a (patch)
parent(WEEKDAY fn) Minor tidy-up for consistency (diff)
(04060109.xhp) Changes to syntax statements to readily identify optional parameters.
Also implemented modification required to CHOOSE function syntax - the Function Wizard allows 250+ arguments, not 30. Change-Id: Ia623c6c908a9efec219cdf8469c26e24ceca0858 Reviewed-on: Tested-by: Jenkins Reviewed-by: Olivier Hallot <> (cherry picked from commit 69ce0305af2d911457274e4889334b335640c5da) Reviewed-on: Reviewed-by: Ilmari Lauhakangas <>
1 files changed, 18 insertions, 18 deletions
diff --git a/source/text/scalc/01/04060109.xhp b/source/text/scalc/01/04060109.xhp
index 061c1db..98463df 100644
--- a/source/text/scalc/01/04060109.xhp
+++ b/source/text/scalc/01/04060109.xhp
@@ -55,7 +55,7 @@
<note id="par_id1027200802301756">The INDIRECT function is saved without conversion to ODF 1.0/1.1 format. If the second parameter was present, an older version of Calc will return an error for that function.</note>
<embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
- <paragraph xml-lang="en-US" id="par_id3154707" role="code">ADDRESS(Row; Column; Abs; A1; "Sheet")</paragraph>
+ <paragraph xml-lang="en-US" id="par_id3154707" role="code">ADDRESS(Row; Column [; Abs [; A1 [; "Sheet"]]])</paragraph>
<paragraph xml-lang="en-US" id="par_id3147505" role="paragraph">
<emph>Row</emph> represents the row number for the cell reference</paragraph>
<paragraph xml-lang="en-US" id="par_id3145323" role="paragraph">
@@ -98,7 +98,7 @@
<h2 id="hd_id3148727">DDE</h2>
<paragraph xml-lang="en-US" id="par_id3149434" role="paragraph"><ahelp hid="HID_FUNC_DDE">Returns the result of a DDE-based link.</ahelp> If the contents of the linked range or section changes, the returned value will also change. You must reload the spreadsheet or choose <emph>Edit - Links</emph> to see the updated links. Cross-platform links, for example from a <item type="productname">%PRODUCTNAME</item> installation running on a Windows machine to a document created on a Linux machine, are not allowed.</paragraph>
<embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
- <paragraph xml-lang="en-US" id="par_id3148886" role="code">DDE("Server"; "File"; "Range"; Mode)</paragraph>
+ <paragraph xml-lang="en-US" id="par_id3148886" role="code">DDE("Server"; "File"; "Range" [; Mode])</paragraph>
<paragraph xml-lang="en-US" id="par_id3154842" role="paragraph">
<emph>Server</emph> is the name of a server application. <item type="productname">%PRODUCTNAME</item> applications have the server name "soffice".</paragraph>
<paragraph xml-lang="en-US" id="par_id3153034" role="paragraph">
@@ -178,7 +178,7 @@
<h2 id="hd_id3151221">INDEX</h2>
<paragraph xml-lang="en-US" id="par_id3150268" role="paragraph"><ahelp hid="HID_FUNC_INDEX">INDEX returns a sub range, specified by row and column number, or an optional range index. Depending on context, INDEX returns a reference or content.</ahelp><comment>UFI: will change with i4904; see</comment><comment>changed by i83070</comment></paragraph>
<embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
- <paragraph xml-lang="en-US" id="par_id3149007" role="code">INDEX(Reference; Row; Column; Range)</paragraph>
+ <paragraph xml-lang="en-US" id="par_id3149007" role="code">INDEX(Reference [; Row [; Column [; Range]]])</paragraph>
<paragraph xml-lang="en-US" id="par_id3153260" role="paragraph">
<emph>Reference</emph> is a reference, entered either directly or by specifying a range name. If the reference consists of multiple ranges, you must enclose the reference or range name in parentheses.</paragraph>
<paragraph xml-lang="en-US" id="par_id3145302" role="paragraph">
@@ -211,7 +211,7 @@
<paragraph xml-lang="en-US" id="par_id3147169" role="paragraph"><ahelp hid="HID_FUNC_INDIREKT">Returns the <emph>reference</emph> specified by a text string.</ahelp> This function can also be used to return the area of a corresponding string.</paragraph>
<embed href="text/scalc/01/04060109.xhp#r1c1"/>
<embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
- <paragraph xml-lang="en-US" id="par_id3149824" role="code">INDIRECT(Ref; A1)</paragraph>
+ <paragraph xml-lang="en-US" id="par_id3149824" role="code">INDIRECT(Ref [; A1])</paragraph>
<paragraph xml-lang="en-US" id="par_id3154317" role="paragraph">
<emph>Ref</emph> represents a reference to a cell or an area (in text form) for which to return the contents.</paragraph>
<paragraph xml-lang="en-US" id="par_id1027200802470312" role="paragraph">
@@ -230,7 +230,7 @@
<h2 id="hd_id3154818">COLUMN</h2>
<paragraph xml-lang="en-US" id="par_id3149711" role="paragraph"><ahelp hid="HID_FUNC_SPALTE">Returns the column number of a cell reference.</ahelp> If the reference is a cell the column number of the cell is returned; if the parameter is a cell area, the corresponding column numbers are returned in a single-row <link href="text/scalc/01/04060107.xhp#wasmatrix" name="array">array</link> if the formula is entered <link href="text/scalc/01/04060107.xhp#somatrixformel" name="as an array formula">as an array formula</link>. If the COLUMN function with an area reference parameter is not used for an array formula, only the column number of the first cell within the area is determined.</paragraph>
<embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
- <paragraph xml-lang="en-US" id="par_id3149447" role="code">COLUMN(Reference)</paragraph>
+ <paragraph xml-lang="en-US" id="par_id3149447" role="code">COLUMN([Reference])</paragraph>
<paragraph xml-lang="en-US" id="par_id3156310" role="paragraph">
<emph>Reference</emph> is the reference to a cell or cell area whose first column number is to be found.</paragraph>
<paragraph xml-lang="en-US" id="par_id3155837" role="paragraph">If no reference is entered, the column number of the cell in which the formula is entered is found. <item type="productname">%PRODUCTNAME</item> Calc automatically sets the reference to the current cell.</paragraph>
@@ -275,7 +275,7 @@
<paragraph xml-lang="en-US" id="par_id3149984" role="paragraph"><ahelp hid="HID_FUNC_SVERWEIS">Vertical search with reference to adjacent cells to the right.</ahelp> This function checks if a specific value is contained in the first column of an array. The function then returns the value in the same row of the column named by <item type="literal">Index</item>. If the <item type="literal">Sorted</item> parameter is omitted or set to TRUE or one, it is assumed that the data is sorted in ascending order. In this case, if the exact <item type="literal">SearchCriterion</item> is not found, the last value that is smaller than the criterion will be returned. If <item type="literal">Sorted</item> is set to FALSE or zero, an exact match must be found, otherwise the error <emph>Error: Value Not Available</emph> will be the result. Thus with a value of zero the data does not need to be sorted in ascending order.</paragraph>
<embed href="text/shared/00/00000001.xhp#regulaer"/>
<embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
- <paragraph xml-lang="en-US" id="par_id3150156" role="code">=VLOOKUP(SearchCriterion; Array; Index; SortedRangeLookup)</paragraph>
+ <paragraph xml-lang="en-US" id="par_id3150156" role="code">=VLOOKUP(SearchCriterion; Array; Index [; SortedRangeLookup])</paragraph>
<paragraph xml-lang="en-US" id="par_id3149289" role="paragraph">
<emph>SearchCriterion</emph> is the value searched for in the first column of the array.</paragraph>
<paragraph xml-lang="en-US" id="par_id3153884" role="paragraph">
@@ -301,7 +301,7 @@
<h2 id="hd_id3153905">SHEET</h2>
<paragraph xml-lang="en-US" id="par_id3150309" role="paragraph"><ahelp hid="HID_FUNC_TABELLE">Returns the sheet number of a reference or a string representing a sheet name.</ahelp> If you do not enter any parameters, the result is the sheet number of the spreadsheet containing the formula.</paragraph>
<embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
- <paragraph xml-lang="en-US" id="par_id3153095" role="code">SHEET(Reference)</paragraph>
+ <paragraph xml-lang="en-US" id="par_id3153095" role="code">SHEET([Reference])</paragraph>
<paragraph xml-lang="en-US" id="par_id3154588" role="paragraph">
<emph>Reference</emph> is optional and is the reference to a cell, an area, or a sheet name string.</paragraph>
<embed href="text/scalc/01/common_func.xhp#sectionexample"/>
@@ -316,7 +316,7 @@
<h2 id="hd_id3148829">SHEETS</h2>
<paragraph xml-lang="en-US" id="par_id3148820" role="paragraph"><ahelp hid="HID_FUNC_TABELLEN">Determines the number of sheets in a reference.</ahelp> If you do not enter any parameters, it returns the number of sheets in the current document.</paragraph>
<embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
- <paragraph xml-lang="en-US" id="par_id3150777" role="code">SHEETS(Reference)</paragraph>
+ <paragraph xml-lang="en-US" id="par_id3150777" role="code">SHEETS([Reference])</paragraph>
<paragraph xml-lang="en-US" id="par_id3153060" role="paragraph">
<emph>Reference</emph> is the reference to a sheet or an area. This parameter is optional.</paragraph>
<embed href="text/scalc/01/common_func.xhp#sectionexample"/>
@@ -330,7 +330,7 @@
<h2 id="hd_id3158407">MATCH</h2>
<paragraph xml-lang="en-US" id="par_id3154896" role="paragraph"><ahelp hid="HID_FUNC_VERGLEICH">Returns the relative position of an item in an array that matches a specified value.</ahelp> The function returns the position of the value found in the lookup_array as a number.</paragraph>
<embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
- <paragraph xml-lang="en-US" id="par_id3159152" role="code">MATCH(SearchCriterion; LookupArray; Type)</paragraph>
+ <paragraph xml-lang="en-US" id="par_id3159152" role="code">MATCH(SearchCriterion; LookupArray [; Type])</paragraph>
<paragraph xml-lang="en-US" id="par_id3149336" role="paragraph">
<emph>SearchCriterion</emph> is the value which is to be searched for in the single-row or single-column array.</paragraph>
<paragraph xml-lang="en-US" id="par_id3159167" role="paragraph">
@@ -351,7 +351,7 @@
<h2 id="hd_id3158430">OFFSET</h2>
<paragraph xml-lang="en-US" id="par_id3149167" role="paragraph"><ahelp hid="HID_FUNC_VERSCHIEBUNG">Returns the value of a cell offset by a certain number of rows and columns from a given reference point.</ahelp></paragraph>
<embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
- <paragraph xml-lang="en-US" id="par_id3159194" role="code">OFFSET(Reference; Rows; Columns; Height; Width)</paragraph>
+ <paragraph xml-lang="en-US" id="par_id3159194" role="code">OFFSET(Reference; Rows; Columns [; Height [; Width]])</paragraph>
<paragraph xml-lang="en-US" id="par_id3152360" role="paragraph">
<emph>Reference</emph> is the reference from which the function searches for the new reference.</paragraph>
<paragraph xml-lang="en-US" id="par_id3156032" role="paragraph">
@@ -391,7 +391,7 @@
<note id="par_id4484084">If LOOKUP cannot find the search criterion, it matches the largest value in the search vector that is less than or equal to the search criterion.</note>
<embed href="text/shared/00/00000001.xhp#regulaer"/>
<embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
- <paragraph xml-lang="en-US" id="par_id3154104" role="code">LOOKUP(SearchCriterion; SearchVector; ResultVector)</paragraph>
+ <paragraph xml-lang="en-US" id="par_id3154104" role="code">LOOKUP(SearchCriterion; SearchVector [; ResultVector])</paragraph>
<paragraph xml-lang="en-US" id="par_id3150646" role="paragraph">
<emph>SearchCriterion</emph> is the value to be searched for; entered either directly or as a reference.</paragraph>
<paragraph xml-lang="en-US" id="par_id3154854" role="paragraph">
@@ -410,7 +410,7 @@
<h2 id="hd_id3149425">STYLE</h2>
<paragraph xml-lang="en-US" id="par_id3150826" role="paragraph"><ahelp hid="HID_FUNC_VORLAGE">Applies a style to the cell containing the formula.</ahelp> After a set amount of time, another style can be applied. This function always returns the value 0, allowing you to add it to another function without changing the value. Together with the CURRENT function you can apply a color to a cell depending on the value. For example: =...+STYLE(IF(CURRENT()&gt;3;"red";"green")) applies the style "red" to the cell if the value is greater than 3, otherwise the style "green" is applied. Both cell formats, "red" and "green" have to be defined beforehand.</paragraph>
<embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
- <paragraph xml-lang="en-US" id="par_id3149302" role="code">STYLE("Style"; Time; "Style2")</paragraph>
+ <paragraph xml-lang="en-US" id="par_id3149302" role="code">STYLE("Style" [; Time [; "Style2"]])</paragraph>
<paragraph xml-lang="en-US" id="par_id3150596" role="paragraph">
<emph>Style</emph> is the name of a cell style assigned to the cell. Style names must be entered in quotation marks.</paragraph>
<paragraph xml-lang="en-US" id="par_id3156149" role="paragraph">
@@ -436,11 +436,11 @@
<h2 id="hd_id3150430">CHOOSE</h2>
<paragraph xml-lang="en-US" id="par_id3143270" role="paragraph"><ahelp hid="HID_FUNC_WAHL">Uses an index to return a value from a list of up to 30 values.</ahelp></paragraph>
<embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
- <paragraph xml-lang="en-US" id="par_id3155425" role="code">CHOOSE(Index; Value1; ...; Value30)</paragraph>
+ <paragraph xml-lang="en-US" id="par_id3155425" role="code">CHOOSE(Index; Value1 [; Value2 [; ... [; Value254]]])</paragraph>
<paragraph xml-lang="en-US" id="par_id3144755" role="paragraph">
- <emph>Index</emph> is a reference or number between 1 and 30 indicating which value is to be taken from the list.</paragraph>
+ <emph>Index</emph> is a reference or number between 1 and 254 indicating which value is to be taken from the list.</paragraph>
<paragraph xml-lang="en-US" id="par_id3149939" role="paragraph">
- <emph>Value1, Value2, ..., Value30</emph> is the list of values entered as a reference to a cell or as individual values.</paragraph>
+ <emph>Value1, Value2, ..., Value254</emph> is the list of values entered as a reference to a cell or as individual values.</paragraph>
<embed href="text/scalc/01/common_func.xhp#sectionexample"/>
<paragraph xml-lang="en-US" id="par_id3150625" role="paragraph">
<item type="input">=CHOOSE(A1;B1;B2;B3;"Today";"Yesterday";"Tomorrow")</item>, for example, returns the contents of cell B2 for A1 = 2; for A1 = 4, the function returns the text "Today".</paragraph>
@@ -453,7 +453,7 @@
<paragraph xml-lang="en-US" id="par_id3148688" role="paragraph"><ahelp hid="HID_FUNC_WVERWEIS">Searches for a value and reference to the cells below the selected area.</ahelp> This function verifies if the first row of an array contains a certain value. The function returns then the value in a row of the array, named in the <emph>Index</emph>, in the same column.</paragraph>
<embed href="text/shared/00/00000001.xhp#regulaer"/>
<embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
- <paragraph xml-lang="en-US" id="par_id3146070" role="code">HLOOKUP(SearchCriterion; Array; Index; SortedRangeLookup)</paragraph>
+ <paragraph xml-lang="en-US" id="par_id3146070" role="code">HLOOKUP(SearchCriterion; Array; Index [; SortedRangeLookup])</paragraph>
<paragraph xml-lang="en-US" id="par_id3148672" role="paragraph">For an explanation on the parameters, see: <link href="text/scalc/01/04060109.xhp#Section9" name="VLOOKUP">VLOOKUP</link> (columns and rows are exchanged)</paragraph>
<embed href="text/scalc/05/empty_cells.xhp#empty_cells"/>
@@ -464,7 +464,7 @@
<h2 id="hd_id3147321">ROW</h2>
<paragraph xml-lang="en-US" id="par_id3154564" role="paragraph"><ahelp hid="HID_FUNC_ZEILE">Returns the row number of a cell reference.</ahelp> If the reference is a cell, it returns the row number of the cell. If the reference is a cell range, it returns the corresponding row numbers in a one-column <link href="text/scalc/01/04060107.xhp#wasmatrix" name="Array">Array</link> if the formula is entered <link href="text/scalc/01/04060107.xhp#somatrixformel" name="as an array formula">as an array formula</link>. If the ROW function with a range reference is not used in an array formula, only the row number of the first range cell will be returned.</paragraph>
<embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
- <paragraph xml-lang="en-US" id="par_id3154916" role="code">ROW(Reference)</paragraph>
+ <paragraph xml-lang="en-US" id="par_id3154916" role="code">ROW([Reference])</paragraph>
<paragraph xml-lang="en-US" id="par_id3156336" role="paragraph">
<emph>Reference</emph> is a cell, an area, or the name of an area.</paragraph>
<paragraph xml-lang="en-US" id="par_id3151109" role="paragraph">If you do not indicate a reference, the row number of the cell in which the formula is entered will be found. <item type="productname">%PRODUCTNAME</item> Calc automatically sets the reference to the current cell.</paragraph>
@@ -509,7 +509,7 @@
<paragraph xml-lang="en-US" id="par_idN11800" role="paragraph">If you use the optional <emph>CellText</emph> parameter, the formula locates the URL, and then displays the text or number.</paragraph>
<tip id="par_idN11803">To open a hyperlinked cell with the keyboard, select the cell, press F2 to enter the Edit mode, move the cursor in front of the hyperlink, press Shift+F10, and then choose <emph>Open Hyperlink</emph>.</tip>
<embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
- <paragraph xml-lang="en-US" id="par_idN1180E" role="code">HYPERLINK("URL") or HYPERLINK("URL"; "CellText")</paragraph>
+ <paragraph xml-lang="en-US" id="par_idN1180E" role="code">HYPERLINK("URL" [; "CellText"])</paragraph>
<paragraph xml-lang="en-US" id="par_idN11811" role="paragraph">
<emph>URL</emph> specifies the link target. The optional <emph>CellText</emph> parameter is the text or a number that is displayed in the cell and will be returned as the result. If the <emph>CellText</emph> parameter is not specified, the <emph>URL</emph> is displayed in the cell text and will be returned as the result.</paragraph>
<paragraph xml-lang="en-US" id="par_id0907200912224576" role="paragraph">The number 0 is returned for empty cells and matrix elements.</paragraph>