•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Data validation drop down not returning all options
Our take
If you're encountering issues with a data validation drop-down not displaying all options from a separate worksheet with 120,000 rows, you're not alone. While autocomplete may work for many entries, certain options can still be elusive. This problem can also affect the filter function on the source worksheet, complicating your data management efforts. Despite attempts with Get and Transform, the solution remains frustratingly out of reach. Let’s explore effective strategies to troubleshoot this issue and ensure your drop-downs function as intended.
Drop down looking at 120,000 rows of a separate worksheet. Auto complete works for many but not all options. The filter option on the worksheet containing the list also falls to return these "problem cells". I've tried using get and transform but still can't get it to work.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Drop down list from discontinuous cells not workingI have to make a bunch of drop downs referencing a seperate sheet that I cant really edit. some of the lists need to reference cells that arent all contiguous. For instance data validation list on =Sheet1!$A$1,Sheet1$C$1, Sheet!$D$1 yields a "You may not use reference operators for data validation critera" But leaving B1 in there works. i.e. =Sheet1!$A$1:Sheet!$D$1 works. Any way around this? submitted by /u/223specialist [link] [comments]
- Returning multiple matches per ID (auto-fill when dragged down?)Hi there! I have multiple assigned IDs for a single code so I need to return all matching values, not just one. I also want this to be kind of automated (?) when I drag down the upstream ID column, it can pull all corresponding values, even if they share or don't share the same code I tried using FILTER, but it only spills results in one place and doesn’t carry over properly when dragged down for other rows. I don’t want to adjust ranges every time manually since my data might get longer. Is there a way to make a formula that: works when dragged down, returns multiple matches per ID, and still updates properly even if the data goes beyond the current rows? Left Side = source workbook; Right Side = destinatioon workbook Thank you so much in advance! submitted by /u/jeankrstein [link] [comments]
- Trying EVERYTHING. I need a drop down list to allow for clickable hyperlinks to external websites.Ok. So I have the drop down list created through data validation (="Report Lists'!$A$1:A$40). It allows me to select the external report names which have embedded URL's. The problem is, it clears the hyperlink on the drop down and makes it plain text. So... What I've found, is to create a =HYPERLINK column next to the drop downs. The problem with that however, is it states "Cannot open the specified file" as it tries to open the text instead of the embedded hyperlink. I cant just directly use the web address as the text name because they are super long, undistinguishable web address names. Any help please? submitted by /u/Gethixit [link] [comments]
- Drop-down list dependant on two other cellsI have a file where I want a drop-down list in cell D14 that is based on cell D10 and D12. I have looked online and saw lot of videos that are dependant on one cell with the indirect formula, but not two. https://preview.redd.it/ptykf9yzrrwg1.png?width=289&format=png&auto=webp&s=6c0fe00c73c3d45816229bcba5fe2f1733bff083 So for example, if I had this random data and I chose Animal in cell D10, Red in cell D12, I would expect to see 33 and 2 in the drop-down menu of cell D14. Is this possible? The data is in another tab called Data and the info is in column F (F3:F1000) that should appear in the drop-down menu of cell D14. D10 and D12 are both drop-down menus if that matters submitted by /u/LookAtThosePepperoni [link] [comments]
Tagged with
#rows.com#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#Excel compatibility#Excel alternatives#big data management in spreadsheets#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#data validation#drop down#worksheet#options#auto complete