Dependant drop list data validation Indirect formula

[FONT=&quot]Hello,[/FONT]
[FONT=&quot]I have a question about data validation. I am new to this forum, apologies if this was asked before. Here is the case.
[/FONT]
[FONT=&quot]I made a table with dependant drop down list, with a data in another sheet. I used the data validation, named ranges and INDIRECT function. Based on choice, the VLOOKUP function then gives the data from the ref table.[/FONT]
[FONT=&quot]And it works just fine.[/FONT]
[FONT=&quot]However, my reference table has grown to more than a 1000 articles and it calculates more than 1000 rows and 20 columns (in the image there is only one, for the simplicity sake). It will grow further.[/FONT]
View attachment test_excel_for.xlsx
[FONT=&quot]So, now every entry, meanin[/FONT][FONT=&quot]g every time I use the two dependant drop down lists, the Excel gets slower. The message it is showing is "calculating thread (number)". And for each entry it takes about 10 to 20 seconds to recalculate.[/FONT]
[FONT=&quot]I have been searching the answer, and several places said that Indirect function in the data validation is what is slowing this process. As a solution, I have tried CHOOSE, INDEX, OFFSET formulas to replace the INDIRECT function, but could not find a working solution. The thing is, all the tutorials are using these functions in columns, whereas my Excel file has only one column, and multiple named ranges in that one row. Can't seem to find up a solution for the Indirect function there.[/FONT]
[FONT=&quot]Is there a way you can help here?[/FONT]
[FONT=&quot]Not to say the least, but this is a huge issue for me, and I am waisting hours on a weekly basis just due to this.[/FONT]
[FONT=&quot]Thank you for your answer.[/FONT]

And here is the Excel file for it.

I appreciate that your sample file is smaller than the real thing, but I am not seeing any recalculation issues with it, and I can't see why you would. At what point do you actually see the calculating message?

Well, basically at every entry.
The real file has 10 more sheets (1-calc, 2-calc, 3-calc and so on) that have manual input (D2, d3 and so on) column. Each input goes like this.
First choose from B column drop down (clothes, vehicles, etc...). Then second drop down is available in column C (based on indirect formula - the second drop down is showing the list of items from the named range clothes, vehicles.. etc.). The last input is the quantity number, which produces the formulas in the E, F and so on ... the multiplier.
Each calculating Sheet (1-calc, 2 calc) is identical, and has about 120 rows. the reference (Table-ref sheet) has currently over 20.000 rows with 20 columns each.
So.
It started as a three sheet file, with some 1000 items in the reference table. Now it has grown (and growing still). Each new entry to the reference table makes it slower.
Now I'm stuck, and it's like quicksand, somewhat.

The data validation is not calculated until you press the dropdown, which is why I asked when exactly you see the message. If you see it after entering a value in a cell and before clicking the dropdown, it's not the data validation that is causing it.