Urgent help needed! - Excel spreadsheet data not pulling through to new tab

Question

Hello!

I need some help with a formula I can't work out.

I have a table with data on tab 1.

I have a new table on tab 2 that I want to bring over the top 10 rows of column B on tab 1. I want this to change when I filter column B on tab 1. So if I filter the data on tab 1, the top 10 results carry over to tab 2.

Could someone please assist?

Thank you in advance

Answer 1

This is Tab 1, with the Filtered column added in column F.

The formula is =SUBTOTAL(2, [@Column2]) where Column2 is the name of the second column of the table.

The formula in B2 on Tab 2 is

=TAKE(CHOOSECOLS(FILTER(Table1, Table1[Filtered]), 2, 5), 10)

where Table1 is the name of the table on Tab 1.

Note that the formula is not in a table - this kind of formula only works in an ordinary range.

Answer 2

That sounds very helpful thank you, however my company has blocked the link. Are you able to share formulas?

Sorry and thank you!

Answer 3

I'd use a helper column, In the demo workbook linked to below, it is named Filtered. This column will return 1 in all visible rows, but 0 in all rows hidden by a filter.

You can use this in a FILTER formula.

See FilteredTop10.xlsx

Answer 4

they are names instead of numbers so I need to filter occasionally to select certain names, and then copy the 10 names that I will select from the filter into the table on the next tab if that makes sense?

It will be column B and the associated data in column E need to move to column B and C on tab 2.

Answer 5

By top 10, do you mean the 10 rows starting from row 1, or the 10 rows with the highest values?