<<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
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
<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



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

Attached Files

Test.xlsx (13.8 KB, 8 views)