Sorting In Cell List Box Using An Excel Formula

0
votes

I have a spreadsheet where I am populating a cell in a worksheet with a drop down list using Data Validation, with a Named Range as the source. The Named Range is based on a worksheet called Services, where the data starts in the second row, the first row being headings.

This works fine where the name has been defined using the following formula:- 

=OFFSET(Services!$A$2,0,0,COUNTA(Services!$A:$A),1)

However, the list is not alphabetically sorted, and all the formulas I have come across on the internet have not achieved this for me.

One site offers a solution which says

If you can handle extreme formulas, here is an all-in-one formula…

However, when I attempt to paste this in to the “Refers To” box when defining the name, it won’t paste (no error message). At first I thought this was because the formula was too long (about 360 characters) but I understand Excel 2003 should handle formulas that are 1024 characters. So I am a bit stuck and would appreciate any suggestions.

I am working for an organisation which is still using Excel 2003, with no immediate prospect of an upgrade.

1
excelexcel-formulaexcel-2003
Does this help at all? get-digital-help.com/2010/10/26/… - Harley B
The problem with that is that it requires a helper column, which might be ok if the data in the worksheet was static, but users will be adding rows sio the sort needs to be dynamic - Cumintrip

1 Answers

0
votes

I think you may be hitting a version limitation (works in Excel 2013) but in any case that name may not help because Data Validation requires a delimited list, or a reference to a single row or column and I think your formula does not provide that.

However, you should be able to create the sorted version with your formula and then name the range that populates - using that Name for your List Source: in Data Validation.