Searchable dependent lists

see if this approach is acceptable - there are no on-sheet formula or hidden data validation - it is all done by a macro triggered by the worksheet_change event.
both the data source and 'Plantilla Coste' are in 'structured tables' if you click in 'A10' a combobox appears for your first selection - it moves to 'B10' to give you a relevant selection then fills the next 3 columns.
to expand the table put any character in 'A11' - move to 'B11' - then back to 'A11' your table has dynamically expanded and the combobox sequence is again available - do not expand the table with blank rows.

I generally solve this kind of problem with pivot tables.

The pivot table sheet has two tables built off TablaProducto.

The first pivot table gets a unique list of providers in the table. The second pivot table gets a list of products associated with the selected provider.

These are overlaid with named dynamic ranges.

Lista_Proveedor =OFFSET(Pivot!$A$4,0,0,COUNTA(Pivot!$A:$A)-1,1)
Lista_Referencia =OFFSET(Pivot!$C$4,0,0,COUNTA(Pivot!$C:$C)-2,1)

These are defined in Formulas > Name Manager

The offset command has 5 arguments.
- Start here
- Go down these many rows
- Go right these many columnsw
- Return a range x rows deep and y rows wide.

So =OFFSET(Pivot!$A$4,0,0,COUNTA(Pivot!$A:$A)-1,1) means
- start in cell A4
- go zero cells down
- go zero cells to the right
- Give me a range, COUNTA(A:A) - 1 rows deep (we do not want to count the header) and 1 column wide.

Dynamic ranges shrink and grow with the data.

Then on the Plantilla Coste sheet, I established an On Cell Selection event on column B. When you select a cell in the range B10:B29, the code is run. The code looks at the provider in column A and does some checking to see if that supplier exists in the database. If it doesn't (as when the cell is blank), then you get an error message. Otherwise, it sets the filter on the second pivot table. This changes the values in the second named dynamic ranges.

Thank you very much for your answers.

I have updated de tables with all the data and it works like a charm. The only issue a have is that there are 200 suppliers and almost 8000 products and it will be most helpful to add a search function to the dropdown list.

I was able to do it myself on the previous sheet with the following formula:

=TRANSPOSE(SORT(FILTER(TablaProducto[REFERENCIA],ISNUMBER(SEARCH('Plantilla Coste'!B10,TablaProducto[REFERENCIA])),"NOT FOUND")))

Any idea on how I could approach this issue?

Thank you very much

I managed to build some code on top of the code I already provided.

Now when you select a cell in Column A a form pops up like the one shown in the image. In the first cell, enter the pattern you would like to match. This can be any of the recognized wildcards. So A*B?? will match anything that starts with A has zero or any numbers of characters followed by a B and two random characters.

Click on the Encontrar Button and the List box is filled out with providers that match the pattern.

Select the one you want, and click on the A la celda Button. This will fill in the cell with the selected provider.

Salida exits the form without taking any action and Despejar clears the cell's contents.

Please excuse any badly labeled items. My knowledge of Spanish is very limited and I am relying on Google translate. If any corrections are needed, please point them out and I will make the adjustments.

You changed the name of the column containing the information. So when you refreshed the pivot table, it came up with a blank pivot table which takes several columns by default, so the blank table was trying to overlap the table next to it.

I fixed this in this version.

Let's try again!