Hello,
I have a question about data validation.
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.
And it works just fine.
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.
So, now every entry, meaning 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.
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.
Is there a way you can help here?
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.
Thank you for your answer.