Quote:
Originally Posted by Tommy7571
It is a solution if I store the matrix data in a physical matrix. Is it possible to have the corresponding matrix as a virtual matrix - without storing it in another physical matrix?
|
What is the meaning of "matrix" in your context? I am not very proficient with german IT terminology (the last time I used it at some elementary level was some 15 years ago in german Excel and Access newsgroups), and I haven't never encountered such use of it!
Generally, on worksheet to refer to some data you must have those data displayed somewhere (in cell, or in table). You can have the data you refer to on hidden worksheet, or in hidden columns of table, but you must have them somewhere.
An exception are named dynamical values, where you create a name which value is calculated based on position of cell the named value is used.
And you can create your own UDF's (User Defined Function), which you can use instead of very complex formulas.
In any of those cases the final formula must read some data from some place in workbook, and must be entered into some cell.
In VBA procedures, you can define arrays (those can have many dimensions). But generally, those arrays exist only until the procedure is running. Probably it is possible define such arrays as global, but anyway you can access them only in VBA.
Quote:
Originally Posted by Tommy7571
Can I access data by VLookUp() ... If I try it I get an error "Value!"... Can you tell me why?
|
The syntax was wrong. It must be
Code:
=IF(tSelection[@Field1]="";"";VLOOKUP(tSelection[@Field1];tSelection;2;0))
, but I'd prefer
=IFERROR(INDEX(tSelection[Field2];tSelection[@RowNo]);"")
VLOOKUP returns entry from 1st matching row. When there is several indentical entries in Field1, you are in trouble. The 2nd formula doesn't have such problems.
Parameters used in lookup are:
1. The value you are searching for in 1st column of search range;
2. The search range;
3. The number of column in search range from where the result is returned;
4. 0 is for exact match - i.e. formula returns an error when search value is not found. Other values for 4th parameter are more difficult to handle, and the search range must be ordered by search value.
Quote:
Originally Posted by Tommy7571
Can you explain a little bit how it works?
You are using a filter function but the formulas are completely new to me...
|
I used Table formulas. With Table I mean the entity defined from menu Insert>Table. With defined Table you can use specific syntax to refer to various regions of Table.
Useful feature of Tables is, that whenever you enter something into any cell immediately below any cell of last row of Table, the Table is expanded automatically. And any formats, conditional formattings, data validations, or formulas are expanded too, until there is only one of kind defined for whole column.