How To Copy A Filtered List In Excel

Contextures

Drop Down from Filtered List

With Excel data validation, yous tin can create drop downward lists, to brand data entry easier. In this example, some employees volition exist on vacation, and their names will be omitted from the drop downwardly, when the employee list is filtered.

Drop Downward from Filtered Listing

Run across how to make an Excel drop downward list show only the visible items from a filtered listing, in this step-by-footstep video. There are written instructions below the video, and yous tin can download the completed sample file.

In this example, the workbook has a list of employees, in a formatted Excel table (tblEmp).

employee list

At that place is a named range – EmpList – based on the Employees column in that table.

named range EmpList

In the sample workbook, at that place is a schedule sheet, with a data validation drop down listing based on the EmpList named range. You can click an arrow in whatsoever jail cell in the Staff column, and select whatever of the employee names.

drop down list of employees

Filtered List

In the Employee table, at that place is a column to mark an X, for anyone who'due south on vacation. To see who'south available for work, you filter the list, and hide all the rows that comprise an X in the Holiday cavalcade

filtered list of employees

Filtering the Employee list does not touch on the data validation drop down list of employees on the Schedule sheet. The drop downs continue to show all the employee names.

filtered list of employees

Number the List

To create an employee listing that just includes the visible rows, we'll clear the filter, and so add a new column (ListNum) to the Employee table, to number the visible rows.

The new column has the following formula, using the SUBTOTAL function. SUBTOTAL ignores rows that are hidden by a filter, so just the visible rows will be numbered

=--SUBTOTAL(three,B$three:B3)

The office has COUNTA (3) as the beginning argument
The range starts at cell B$three (locked row), and goes downwardly to the current row, B3 (non locked)
The two minus signs at the start of the formula will prevent problems when filtering the tabular array. Without them, Excel always leaves the last row visible.

Here is the table with all rows visible, showing the SUBTOTAL formula results in each row.

To evidence how the numbering formula works, there are cells at the top that link to the commencement ii rows, so nosotros can check the results, after some of the rows are hidden.

Note: Those formulas are for demo but, they are non needed in the final workbook

numbered list of employees

To test the SUBTOTAL formula, filter the Employees table, to hide the rows with an X in the Vacation column. The numbers alter, and the visible rows are numbered sequentially.

The SUBTOTAL function ignores rows that have been hidden by a filter, then it volition only summate a count on visible rows. The formulas at the top show that in that location is a 0 result for the SUBTOTAL function in the hidden row for D3

numbers for visible rows only

Make a New List

Now that the total listing is numbered, we'll create a second list of employees. The new listing will take formulas, to become the names, based on the numbers in each row.

The new list has the headings, ID and Emp, and numbers are typed in the ID column. Be certain to add at least as many numbers as there are employees in the main listing – I added a few extra numbers too.

In the Emp column add this formula, to return the employee name, based on the ID numbers.

=IFERROR(INDEX(tblEmp[Employees], MATCH(B2,tblEmp[ListNum],0)),"")

The Index function returns a name from the Employees cavalcade, for the row that has the same number every bit the ID.

This screen shot shows the new list, with the numbered names from the filtered principal list.

numbers for visible rows only

Dynamic List of Names

We want to utilize this curt list for our drop downwards list, so we'll create a dynamic named range with the names in our new list.

To brand the named range the correct size, a formula on the worksheet get the number of visible names.

To get the count, the cell contains this MAX formula:

=MAX(tblEmp[ListNum])

That returns the highest number from the main listing.

MAX function

Create a Dynamic Named Range

Next, to create a dynamic named range, that will accommodate its number of rows based on the count of visible names, follow these steps:

On the Ribbon'due south Formulas tab, click Define Proper name
Type the name: EmpListAvail
For the Scope, select Workbook
In the Refers to box, enter this formula:
=ListDV!$C$2:INDEX(tblEmpAvail[Emp], ListDV!$F$1)
Click OK

The formula for the dynamic range starts at cell C2 on the ListDV sheet.

=ListDV!$C$2:

And then, the Index function returns the finish cell in the Emp column, based on the number in cell F1

INDEX(tblEmpAvail[Emp], ListDV!$F$1)

If that number changes, the cease cell will arrange to the the new number.

create a dynamic range

In the Name Managing director, to check the Dynamic Range, follow these steps:

On the Ribbon's Formulas tab, click Define Proper name
Click on the new proper noun: EmpListAvail
Click in the Refers to box, and the range volition be highlighted on the worksheet, with "marching ants".
Click Shut.

check the dynamic range

Change the Drop Downward Lists

The final step is to change the drop downwards lists, so they are based on the dynamic list of employees, instead of the full list of names.

On the Schedule canvass, select all the cells that take the drop downwards listing for employee names
On the Information tab of the Ribbon, click Data Validation
In the Source box, refer to the dynamic named range: =EmpListAvail
Click OK

change the drop downs

Now, examination the drop downward list, and information technology should show the brusk list of available employees, instead of the full list.

test the drop downs

Notation: If names were previously selected from the full listing, and are not in the filtered list, you might see Data Validation error warnings.

data validation error warnings

Get the Sample File

Get the completed workbook for the Excel Drop Downwards from Filtered List tutorial. The zipped file is in xlsx format, and does not incorporate macros.