Data validation list sorting - removing blanks
Hi,
I have a named range that is dynamic and want to remove the blanks from a dropdown DV list.
I've tried using an OFFSET formula but it doesn't sort alphabetically even if the data is sorted as such.
How can I either remove the blanks in the list or sort alphabetically when using OFFSET?
Please Login or Register to view this content.
Thanks,
M
I usually create a pivot table, which aggregates AND sorts AND removes blanks then reference the pivot table with your offset formula.
Please ensure you mark your thread as Solved once it is. Click here to see how.
If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
Where are the blanks? Are they scattered within the range or at the bottom of the range?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Where are the blanks? Are they scattered within the range or at the bottom of the range?
At the bottom of the range. I wrote a macro to extract unique instances from a column and sort into a range. But I want to be able to expand this range if needed if, for example, new instances are added to that column.Originally Posted by Tony Valko
Bumping post
Can you not use the VBA to wrap a named range around the list?
Also if it was blank wouldn't it already not be counted with the COUNTA?
This would have been solved already if you posted an example file...
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).
Maybe this...
=OFFSET(Sheet3!E3,1,,COUNTIF(Sheet3!E3:E2001,"?*"))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks