PDA

View Full Version : [SOLVED] Sorting drop down box output



newk
05-10-2005, 02:27 AM
(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
05-11-2005, 11:12 PM
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
05-12-2005, 01:39 AM
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
05-12-2005, 04:02 PM
Hi


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


Tony

newk
05-16-2005, 01:48 AM
Hi Tony,thats great and really does the job, thanks a lot for all the help.