Sorting drop down box output

[newk]

(Please refer to the attached spreadsheet)

On worksheet ?Test Matrix? at the top I have a drop down box with 3 options; ?ABL, Factoring & ID?.

When an option is selected it looks up worksheet ?All tests? and displays the tests required.

However the output is displayed out of order and ?Factoring & ID? options leave unwanted gaps in between the tests.

I would like to be able to sort the tests on worksheet, 'test matrix' by column C and remove any unwanted gaps.

I have tried macros but to no avail. Please could someone help me!

[acw]

Hi

Try this

1) Change the formula in B4 to be

=IF(VLOOKUP(A4,'Mandatory test - normal scope'!$A$3:$F$64,VLOOKUP($C$1,{"ABL",4;"ID",5;"FACTORING",6},2,FALSE))="y",VLOOKUP(A4,'Mandatory test - normal scope'!$A$3:$F$64,2),"")

Copy down to B65

2) Remove the formula from B66
3) Add the following code to the drop down box

Private Sub ComboBox1_Change()
 Application.EnableEvents = False
 Sheets("test matrix").Range("a4").Select
 Range("a4:c65").Sort key1:=Range("c4"), order1:=xlAscending, header:=xlNo
 Application.EnableEvents = True
End Sub

Tony

[newk]

Thanks Tony that is very cool, it has improved the sheet 100%.

I still have a little problem. When I select, 'FACTORING' it still leaves some rows. Do you know how to get rid of these?

[acw]

Hi


It doesn't in my file. Have a look at the attached and let me know.


Tony

[newk]

Hi Tony,thats great and really does the job, thanks a lot for all the help.