<<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.
Ok. Anyway it is not a problem to create a hidden table somewhere and it is better. Perhaps I confued with programming techniques and I don't want to use these. So I will do it like this.
<<The syntax was wrong. It must be
Code:
=IF(tSelection[@Field1]="";"";VLOOKUP(tSelection[@Field1];tSelection;2;0))
Thanks very much I don't even understand why I wrote this formula like this. Perhaps because I am not very used to use the VLookUp -function...
<<, but I'd prefer
Code:
=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.
To have the same entry two times is not reasonable in my case. As far as I know the iferror function is not recommended since it often gives an error without giving a hint on the error.
<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.
Great! Thanks very much. I never used such type of tables and it is very interesting.
I established the same table in my excel sheet and put the filter function in each of the consiedered table columns. I created a range named "Analyte" in the name manager and even took the same names for the considered columns.
As in your table tData, I put the function on the right of the table in the column RowNo.
=ZEILE()-ZEILE($D$7)+1 works but
ZEILE()-ZEILE(Analyte[[#Kopfzeilen];[RowNo]]) doesn't :
I used the following article to study the method:
https://www.huegemann-informatik.de/...excel-tabellen
According to this article there should be no problem with my function...
However, I still get the same error. Hints are so general that they do not help...
the following code
Code:
=WENN([@Selected]="x";ZÄHLENWENNS([Selected];"x";[RowNo];"<=" & [@RowNo]);"")
has been put in the column SelectedRowNo. It also gives an error.
What is @Selected ? Where is it defined?
In the attachment I deleted = to avoid error messages.
I did not even try to work on the table selections...
Any idea where the problems could be since the tables of your excel file works...
I wasted hours for this and see no solution.
Thanks
Tommy7571