Thread: How to do you sort a range for a List Box

Hi everyone

Thanks to rylo, my last problem was resolved successfully. I am now expanding my workbook to produce a specific customer history worksheet.

As part of this, I created a list box linked to a single column of data (numbers) using a named range. So far, so good. The source data is located on another worksheet in the following format:

NUM

758042
805717

754464
758073

802936
831503
773692

808884

859220
765972
768063

785565

779603
837646
813189
767925

The order of this data cannot be changed on this worksheet and includes varying blank rows as above.

Ideally, I would like this set of numbers to appear in the list box without blanks and for it to be sorted in ascending numerical order.

I have tried adding formulas to the range in the NAME setup but without success. Data Validation does not provide the answer and I have been unable to find a solution after searching through this site and some others.

I would also prefer not to have to create a second column somewhere that shows the results sorted and minus blanks, if at all possible, hence the search for a formula or function or even a macro.

Hopefully, there is someone who does know the solution.

spellbound

Hello Spellbound:

Assuming your data is in column A, paste this formula in column B and copy down as far as needed.

=IF(ISERR(SMALL(A:A,ROW(1:1))),"",SMALL(A:A,ROW(1:1)))

Select your range in column B, say B1:B50, then open the Define Name Dialog box and at the bottom where it says, Refers to: type in this formula.

=OFFSET(B$1,0,0,COUNT(B$1:B$50),1)

Enter a name, “NUM_B” and click the OK button to exit.

Now in the Format Control of your List Box where it says, “Input range:” enter the range name NUM_B.


Matt

Hi Matt

Thanks for the advice; unfortunately it is not a practical solution in my workbook as the sheet containing the column of data to work from is set out in such a way that it includes other data not required.

However, this has triggered me to change the way my workbook is set up and I am creating a main data input worksheet from which will be linked to all of the other worksheets in different ways.

This immediately resolves my list box problem as the Input Data will be sorted by the customer ID number, which is linked to the list box by a named range.

Before I did the final sort on this column, I thought that I would try out your solution because it may always be useful in some other scenario.

I created the 2nd column using (without the extra space):

=IF(ISERR(SMALL(A:A,ROW(1:1))),"",SMALL(A:A,ROW(1:1)))

and then created the name range using:

=OFFSET(B$1,0,0,COUNT(B$1:B$50),1)

and finally entered the range name in the Data Validation for the list box.

The result was that there was nothing in the list box.

However, if I replace the formula in the name range with the column containing the data, it works fine.

Not sure what the problem is but would like to know, in case I still need to use this for another problem in sorting out addresses.

Thanks for taking the time to help me out ...spellbound

Sorry about that Spellbound, I should have read your post more carefully.

About the OFFSET function, make sure the range in question is selected first before typing in the function.

The entire column will work just as well, not sure why I thought a specified range was necessary.

=OFFSET(B$1,0,0,COUNT($B:$B),1)

Other than that it should work.

Matt

No problem Matt, it might be because the initial task was based on data that would have had to come from a specified range.

As the source data is now sorted by virtue of the way it is inputted, there is no need to make use of the first part of your solution.

Using the new source data, I created a named range using A2:A499 to avoid the column header appearing in the list box.

However, I am still a bit confused as to whether there is an advantage to using your formula:

=OFFSET(B$1,0,0,COUNT($B:$B),1)

in the formula box for defining the range, when simply using the column reference seems to work in exactly the same way.

i.e. =$A$2:$A$499

This is not meant to be a form of criticism, it just would help me to understand the use of this formula better.

David (spellbound)

David,

In your original post you stated that you wanted the list of numbers to appear in the list box without blanks and for it to be sorted in ascending numerical order.

The idea behind the OFFSET function is to be used in conjunction with the first formula (column B).

=IF(ISERR(SMALL(A:A,ROW(1:1))),"",SMALL(A:A,ROW(1: 1)))

If you should add or delete numbers from the range (column A) your list box will populate according to the number of entries found in column B.

However, this web page can explain it far better then I.

http://support.microsoft.com/default...b;en-us;830287


Matt