To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=81851
Issue #|81851
Summary|It seems impossible to convert a text into an address
|and therefore it is apparently impossible to calculate
| addresses / I cannot use a 'calculated' or 'condition
|al' address (text) as corner point for an array / Look
|up functions are insufficient / LOOKUP() should work a
|lso with unsorted lists
Component|Spreadsheet
Version|OOo 2.2.1
Platform|All
URL|
OS/Version|Windows XP
Status|UNCONFIRMED
Status whiteboard|
Keywords|
Resolution|
Issue type|FEATURE
Priority|P3
Subcomponent|ui
Assigned to|spreadsheet
Reported by|ghuoargh
------- Additional comments from [EMAIL PROTECTED] Sat Sep 22 18:57:56 +0000
2007 -------
I have a 'reference table' wich contains a type of different codes for a certain
number of numbers, however, any other database-like table is, in principle,
affected by the same problems. (This table has several rows and columns. The
columns contain the different code-number sets, and the rows the values by which
one particular original number (= y-coordinate), which is in the first column,
are to be replaced unter particular conditions, which are stated in the first
row by a case number (= x-coordinate).)
In another table I want to lookup values from this table, whereby the the value
to be shown or used depends on both coordinates of the 'code table'. In this
table as in most other tables, not all columns can be sorted at the same time,
therefore, I cannot use the function LOOKUP. An alternative is the related
spreadsheet function VLOOKUP, however, for this, the data with the search
criterion must be in the first column of the array, which contains the 'input
reference' as well as the output value. Yet, in my case the search criterion has
to be searched in different columns depending on another value which in the
'code table' constitutes the x-coordinate. Thus I can use VLOOKUP only, when I
introduce a conditional function which changes the range of the array and
thereby its first column depending on these other value.
I found an expression which should fulfill these requirements. It is:
= VLOOKUP ( $X11 ; CONCATENATE(REPLACE(ADDRESS(31;(MATCH($B11;'Sheet
2'.$B$30:$P$30)+COLUMNS('Sheet 2'.$A$30:$B$30)-1);1;"Sheet 2"); 1; 10; "'Sheet
2'");":$T$36") ; COLUMN('Sheet 2'.$Q$30)-COLUMN('Sheet 2'.$B$30)+1 ;
FALSE() )
Yet this Function does not work, albeit its elements return, in principle, the
correct values :
a = $X11 = 1 [= the search criterion ]
b = CONCATENATE(REPLACE(ADDRESS(31;(MATCH($B11;'Sheet
2'.$B$30:$P$30)+COLUMNS('Sheet 2'.$A$30:$B$30)-1);1;"Sheet 2"); 1; 10; "'Sheet
2'");":$T$36") = 'Sheet 2'.$B$31:$T$36 [= the 'conditional' array; its
left border and therewith the first column, in which the values corresponding to
the search criteria must be, depends because of this expression on the content
of the cell in the same row as the function (which is here row 11) and in column
B (since "Match($B11;...)" ]
c = COLUMN('Sheet 2'.$Q$30)-COLUMN('Sheet 2'.$B$30)+1 = 16 [= the "index"
number within the array which contains the output values; the complicated
expression I have chosen to provide for the case that I change something in the
code table so that the colums are shifted ]
d = FALSE() = FALSE [this statement is required in case that the values
are not sorted, which is the case in my example)
But when these values are put together as arguments into the function
VLOOKUP(a;b;c;d), these function returns "Err:504" as in the case where these
functions themselves have been put into the brackets of VLOOKUP.
The reason for this appears to be, that the string » 'Sheet 2'.$B$31:$T$36 «
yielded by the function b is not recognized as an address be sCalc. Thus, a
function which converts an address text into a real Calc address seems necessary
... particularly incases where, like in this one, the addresses to be used are
the result of computations ...
---------------------------------------------------------------------
Please do not reply to this automatically generated notification from
Issue Tracker. Please log onto the website and enter your comments.
http://qa.openoffice.org/issue_handling/project_issues.html#notification
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
