LinkBack URL
About LinkBacks
Hello
I am looking for a formula using index for dynamic data validation list.
there are numbers , blank cells , alphabet
The data is very bulky.
Can someone assist
Hello
I am looking for a formula using index for dynamic data validation list.
there are numbers , blank cells , alphabet
The data is very bulky.
Can someone assist
Jean1972 - you have been a member of these forums for long enough now to know that a failure to provide your expected outcomes, manually added, will mean that it is difficult for those wishing to help to offer a solution. Please provide a fresh version of the sample sheet with your expected results added.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
copy the data of column A in a new column.
Use remove duplicate => data => remove duplicate
the sort the data A - Z => data => sort A-Z
Then use the new datalist on your data-validation.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Sorry about that .
What I mean how to make it dynamic using index as I cannot alter the data
amended file in green cell vlaidation data
[CODE]
=Sheet1!$A$3:INDEX(Sheet1!$A$3:$A$1000,COUNTA(Sheet1!$A$3:$A$1000))
[CODE]
There is no direct way to do what you request that I am aware of. Preparation steps are required.
See the modified upload.
Here is a new dynamic named range replacement for RANGE4.Then for a new drop down source range this formula array entered in M1 and filled down.Formula:
Please Login or Register to view this content.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
Formula:Please Login or Register to view this content.
Then this dynamic named range for the drop down list: DD_ListWith E2 the active cell click Data >> Data validation >> Data validation, then Allow: List, Source: DD_List >> click OK.Formula:Please Login or Register to view this content.
Your drop down is complete and without blanks.
Dave
FlameRetired, Thank you, I was looking to sort out this issue more than 3 days as it is a big spreadsheet.
your approach is very good to this type of situation
You are welcome. Glad to help. Thank you for the feedback and for marking this thread Solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks