Extact Top 20 rows DYNAMIC

[LORDMARKS]

Hi all,

I have been looking for a nice way to extract the top 20 rows based on the value in one column.

The data table is approx 20 columns by 6000 rows, I use VBA to sort and apply filters in various ways, but I would like a way of keeping a small table at the top that keeps the top ten of the filtered values?

Is this possible or do I need to copy the data accross via vba


Thanks for any ideas

 

[bschwartz]

A formula solution is possible to provide top 10, or top 20 (you seem to be asking for both?), however, I don't think a formula solution will care about filters. It will simply return the top values.

Have you considered using a PivotTable for your analysis?

 

[LORDMARKS]

Sorry for the confusion, Its top 20 I'm after, As far as I can tell, your correct and formulas want ignore filtered values.

I was thinking to copy, paste and sort to another sheet and then ref# to that...unless there is a reason why this is a bad idea?


Also the data uses approx 40 buttons to run various filters, would i be better off using drop downs and one button to lookup?