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

When you refer to whole datarange (i.e. all except header row) of named Table, you use Table name as reference. I.e. when you defined Table tData, then in formulas tData refers to data in this Table;
To refer to data in specific column in Table outside of this table, the reference will be like tData[ColumnHeader];
To refer to data in specific column in Table from same Table, the reference will be like [ColumnHeader];
To refer to cell in same row in same Table the reference will be like [@ColumnHeader].
(So @Selected refers to cell in column Selected positioned in same row as formula.)

There is more - try to google for "excel table formula syntax".

Btw, when you have formulas which use Table formula syntax, and you change Table name, or column header - this change is applied to all formulas using Table refernces automatically.

As about error you formula returns - post here the example workbook with faulty formula (NB! A small one!)! Otherwise it is difficult to help with this.

Edit: When using Table syntax, try to avoid spaces in column names. Otherwise you have to use much more squared brackets in formula, and formulas are more difficult to read. And with longer formulas, excel will force line breaks into formula field at top - so unless you make the formula field higher, you see only a part of formula at once.

<<There is more - try to google for "excel table formula syntax".


Tahnks for the hint.



<<As about error you formula returns - post here the example workbook with faulty <<formula (NB! A small one!)! Otherwise it is difficult to help with this.


I put the file as an attachment but it seems that something has gone wrong, if you didn't find it.Now I put it again...



Thanks


Tommy7571

Attached Files

Test.xlsx (13.8 KB, 9 views)

1. You defined a Named Range Analyte (from menu Formulas>Name Manager, or you simply selected the region and entered range name into Range field at top). This is not Table - you can refer to it as whole, or you can use INDEX() function to refer to part of it, but it is doesn't have any defined inner structure like Table has. As nams Analyte was used, I additionally defined a Table tAnalyte.

2. Having "=" removed from formulas didn't be any help. Now I had german function names there.

3. When you want to have some additional header rows for table, leave a hidden empty row between bottom one and rest of them. A Table can have only a single header row, and it tries to incorporate all adjacent rows and columns automatically (especially at right and at top, but better is to be on safer side).

4. Change the column header "Volumen [µl]". Table reference syntax uses square brackets, and having them in column name also may cause problems.

5. Never put any summaries at bottom of Table (or at bottom of any table)! Reserve them some rows at top of page instead! And then use Freeze Panes to keep summaries and column headers always visible. You see always table headers and totals, and you haven't to bother with adding empty rows manually, or editing your formulas when standard references are used, whenever you add some new info.

I also defined a Data Validation List for Selected column, so it is possible to enter "x", or to leave cell empty, but not anything else.

Try out: change column header "Selected" to "Gewählt", and look what happens with formulas!

Attached Files

AuswahlTest.xlsx (14.5 KB, 7 views)

Hello,


after much time that I spent on this issue I can gladly say that it finally worked.
Thanks very much. It was the first time that a used such a table in Excel.


Thanks


Tommy7571