Data Validation

[Naveenaggarwal]

Hi!
I need a support on data validation.
I have a list for data validation and i want data validation in multiple cell but the value of previous data validation list should be removed automatically from next list.

enclosing sample sheet for understanding.

Thanks
Naveen Aggarwal

 

[Alex Blakenburg]

You might want to update your profile to show what version of Excel you are using.
You will find a number of ways to do it here on Debra's Contextures site. The easiest option will be if you have MS 365.

Excel Data Validation Hide Used Items

 

[Zinc Copper]

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.

 

[Akuini]

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.

Are you open to using VBA?
I have an example of multi-level dependent data validation, with a macro to create a helper table.
• You can easily set up 2 or more level dependent data validation as needed.
• You only need 1 main table as the source, 1 helper table (created by macro), 1 named range & 1 UDF.
• The code will generate a non-empty, unique & sorted data validation list. The list is also dynamic, you can add data on the list without having to change the data validation formula.
I tested it with 20K rows of data with 20 category in it, it worked without any lag.
Let me know if you’re interested in this method.

 

[Zinc Copper]

Thank you for your kind reply.
I am daily trying to find what to do to solve this.
Please look at the images 1 and 2. The image 2 shows how the data is classified - I have named ranges "vehicles", "clothes" ... and those are important part of the whole thing. When I expand the list (table) on image 2, I only expand a single name range.
That way, if I had 20k rows, when I would open drop down menu, I would have to choose between 20k items. That is why there are more named ranges. In the full table data that I use, there are more than 40 named ranges (and growing, if some other group appears).

 

[Akuini]

@Zinc Copper
You didn't answer my question, are you ok to use macro?
If yes then could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.

it calculates more than 1000 rows and 20 columns (in the image there is only one, for the simplicity sake)

If you could upload a sample workbook, could you provide an example with 2 columns in it?

 

[Zinc Copper]

Thank you for the suggestion, I was looking for a way to upload the file.

I'm fine with using the macros, just not very good at writing them.

The link for the file is here:

test_excel_for.xlsx

Shared with Dropbox

www.dropbox.com

 

[Akuini]

I've downloaded your sample file.
Are you sure that the Indirect function in data-validation is the cause of the slow down?
For debugging purpose, on a copy of the original file, try this:

1. remove all data-validation in col C
2. enter some data in col C manually (without data-validation)

if the file is still slow then the cause of the slow down is not the indirect function.