Results 1 to 5 of 5

Thread: Dependant drop list data validation Indirect formula

  1. #1
    Neophyte Zinc's Avatar
    Join Date
    May 2022
    Posts
    3
    Articles
    0
    Excel Version
    2016

    Dependant drop list data validation Indirect formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,
    I have a question about data validation. I am new to this forum, apologies if this was asked before. Here is the case.

    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.
    test_excel_for.xlsxClick image for larger version. 

Name:	5.jpg 
Views:	9 
Size:	44.9 KB 
ID:	10927Click image for larger version. 

Name:	1.jpg 
Views:	9 
Size:	55.9 KB 
ID:	10928Click image for larger version. 

Name:	4.jpg 
Views:	9 
Size:	96.6 KB 
ID:	10929
    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.

  2. #2
    Neophyte Zinc's Avatar
    Join Date
    May 2022
    Posts
    3
    Articles
    0
    Excel Version
    2016
    And here is the Excel file for it.Click image for larger version. 

Name:	4.jpg 
Views:	8 
Size:	96.6 KB 
ID:	10930Click image for larger version. 

Name:	5.jpg 
Views:	7 
Size:	44.9 KB 
ID:	10931Click image for larger version. 

Name:	1.jpg 
Views:	6 
Size:	55.9 KB 
ID:	10932Click image for larger version. 

Name:	2.jpg 
Views:	6 
Size:	66.1 KB 
ID:	10933Click image for larger version. 

Name:	3.jpg 
Views:	7 
Size:	26.8 KB 
ID:	10934

  3. #3
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    205
    Articles
    0
    Excel Version
    2010
    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?
    This website wants to know your momentum - | Deny | | Allow |

  4. #4
    Neophyte Zinc's Avatar
    Join Date
    May 2022
    Posts
    3
    Articles
    0
    Excel Version
    2016
    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.

  5. #5
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    205
    Articles
    0
    Excel Version
    2010
    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.
    This website wants to know your momentum - | Deny | | Allow |

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •