Hi, I came to this forum looking for some help after I watched a video on dependant drop down menu alternatives.
I have this downtime sheet that I've stripped down. It's part of a much larger workbook. The issue is this sheet is extremely slow to process. The drop down menus have visual delay with a "thinking wheel" before the menu is presented. After researching, I'm sure it's the indirect usage in the data validation. I have about 2000 rows in the normal sheet. There is also a large name manager setup too.
Is there a way to fix this or convert what I have into a newer alternative to make things run normal? I watched the video but I'm not confident I follow what was shared. This workbook is on sharepoint and there could be a handful of users on this workbook interacting at any one time. The users could be using desktop excel or online excel.
I tried to attach a sample file but I get an error.
Thanks for the feedback.
If you can't upload a file (though it should work), please share a link that gives access to the file on OneDrive or similar.
And please let us know exactly which video you watched.
@cipow Sorry, the anti spam settings were blocking XLSX files. I've fixed that now
Thank you I will try and attach again. I watched The Excel Function You Should STOP Using (Alternatives)
@cipow OK! That clarifies the matter. The example in that video shows how you should structure a table that contains the data validation (DV) options. In that case a table with two columns with country names repeating in the first column with all regions in the second. Thus, two levels of DV.
In your data there is a DV structure for Area and it is 4 levels deep (Area, Sub-area, Section and Sub-section). So, you'll have to build a table (name it sensibly) with 4 columns. You need to do that for each set of DV structures with the correct number of columns. Your file has a total 120 1-column tables, most with not very useful names like "Table156". In your real file these are probably all referenced with INDIRECT in DV.
It will be quite an effort to reorganise all the small table into larger ones as described in the video. Perhaps you can try it first with some DV structures that only have two levels and follow along with the video. Obviously, you won't notice any performance improvements until you get rid of all the named ranges and indirect DV's.
Hi thanks for the response and direction to start looking in. I think this will end up being simply over my head and might just leave performance as is, since it works, at the cost of performance. There are many named ranges indeed and yes are all used over the workbook. Maybe I can look for a programmer student to check it out.
