Get top 10 values from a summary sheet bases on 1 condition

[batchjb69]

Hi

I've created the attached and want to get the top 10 values from column E of the INPUT sheet when a different name is selected in the drop down list in D1 on the summary sheet. I also want the relevetnt house number, Street name and Pososte code to populate I want to do this using VBA. How would I do this.
top%2h010(1).xlsm
Please see attached.

Any help much appreciated

Rgds,

[PCI]

What about a pivot table and why not a macro to refresh automatically!

[FDibbins]

I know you have this in the VBA forum, but this ARRAY formula, copied down and across, will also give you what you want...

=INDEX(INPUT!B$2:B$185,MATCH(LARGE(IF(INPUT!$A$2:$A$185='Summary Sheet'!$D$1,INPUT!$E$2:$E$185,0),'Summary Sheet'!$A4),INPUT!$E$2:$E$185,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[alansidman]

Here is a VBA solution option for you,

Please Login or Register  to view this content.

[batchjb69]

Thanks ALan I get an error "unable to get the text property of the range class when the macr reaches the below code


cbox = w1.Range("D1").Text

[alansidman]

Unable to reproduce your error. Here is the sheet that I tested code on. If you are using a different sheet then, you will need to ensure that the layouts and formats are the same as the original, or upload the wkb you are having issues with for analysis. Is your Cell D1 in fact text (a string) or is it something else (ie. number or date?).

[batchjb69]

I see

I Know why I put your code in the sheet module as I want the macro to automatically run every time the drop down box is changed.

[alansidman]

I guess if you told us the whole story at the beginning then you would get code that you wanted.

If you want it to run on worksheet change, then you need to put it in the worksheet change event and not as a module. You will have to identify the target as D1. You will need to add this to the code. Look here for understanding how the worksheet change event works

http://msdn.microsoft.com/en-us/libr...ffice.15).aspx