Home > Excel Tutorial > Advanced Filtering With Drop Down Lists

Advanced Filtering With Drop Down Lists

         In this template ,other page (second sheet) was used to filter data. Also ,drop-down lists (Data_Validation_List) are used to facilitate filtering.

The drop-down lists are filled with unique values. Also named ranges are used as sources of data validation (Col1,Col2,Col3 etc.).

When there are changes in the data sheet, may be necessary updating the unique values. so when the book is opened ,”unique values” macro runs automatically :

Private Sub Workbook_Open()
Call unique_values
End Sub

Or unique values can be updated by pressing the button on the “Unique Values” page.

Any updating doesn’t need for the named ranges  .Because the codes of named ranges were defined accordingly.For example :

ActiveWorkbook.Names.Add Name:=”Col1″, RefersTo:=”=OFFSET(Unique_Values!$A$2,0,0,COUNTA(Unique_Values!$A:$A)-0)”

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: