Use a calendar to filter an Excel defined Table
This article demonstrates how to filter an Excel defined Table based on the selected cell in a calendar. The calendar highlights dates containing appointments or events, the clear button below the calendar deletes all filter conditions.
My previous post Excel calendar (vba) used a formula to extract events based on selected cell, this article shows you event code and a macro to filter the Excel defined Table itself.
How to use this workbook
The animated image below shows what happens with the Excel Table when you select different cells in the calendar, it also shows what happens when you press with left mouse button on the "clear filter" cell.
The Excel defined table and calendar, unfortunately, have to be located like the image shows above. The Excel Table will filter out calendar rows if they are next to each other.
I would certainly have liked the calendar above the table but that makes it impossible to adjust the calendar's cell widths to the same size.
How I created this workbook
The calendar is dynamic meaning it changes automatically based on the selected year and month.
Create calendar
See steps in my previous post.
Conditional formatting
For some reason I don't understand, a table name is not allowed in a conditional formatting formula . I ended up creating a named range:
Update: You can use the INDIRECT function in order to use Excel defined Tables in Conditional Formatting formulas, you can read more about it here: How to use a Table name in Data Validation Lists and Conditional Formatting formulas
Conditional formatting formula:
Conditional Formatting formula for highlighting days containing events:
See remaining steps in my previous post.
Vba code
'Event code that is triggered when the user selects any cell in cell range E4:K9 Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Check if selected cell is in cell range E4:K9 and the number of selected cells are 1 If Not Intersect(Target, Range("E4:K9")) Is Nothing And Target.Cells.Count = 1 Then 'Apply a filter to first column in Excel table named Table1 based on selected cell's date ActiveSheet.ListObjects("Table1").Range.AutoFilter _ Field:=1, Criteria1:=">=" & Target.Value, Operator:=xlAnd, Criteria2:="<" & Target.Value + 1 SortTable 'Clear filter if cell G11 is selected ElseIf Not Intersect(Target, Range("G11")) Is Nothing Then ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1 'Run macro SortTable SortTable End If End Sub
Sub SortTable() 'Sort table in an ascending order based on first column With ActiveWorkbook.Worksheets("Calendar").ListObjects("Table1").Sort .SortFields.Clear .SortFields.Add Key:=Range("Table1[[#All],[Start]]"), SortOn:=xlSortOnValues, Order:= _ xlAscending, DataOption:=xlSortNormal .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub
Where to put the VBA code?
- Press with right mouse button on on the sheet name, it is located at the very bottom of the Excel screen.
- Press with left mouse button on "View code".
- Paste event code to sheet module.
- Return to excel.
How to add new records to the table
- Select the last cell in the table
- Press Tab key
- Enter new values
New records are also highlighted in the calendar. Make sure you have selected the correct month to be able to see them.
Recommended blog posts
Filter a table using the selection change event
Extract unique distinct values from a filtered table (udf and array formula)
Calendar category
This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
Functions in this article
More than 1300 Excel formulas
Excel categories
6 Responses to “Use a calendar to filter an Excel defined Table”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
" a table name is not allowed in a conditional formatting formula."
It is, but it has to be used like this:
=INDIRECT("Table1[Start]")
David Hager,
Thanks!!
This file Table and calendar.xlsm, is not workink for me.
Yoram,
What happens?
Hi,
I deal with this issue constantly, and I cant seem to find any solution for this. I hope you can help me out. I have a set of daily events for 80 days straight. I have written down events from day 1-80. I am looking for a file where I set the starting date, month and year, and the file creates a calendar (or a list) from day 1 for the following 80 days. Then export the calendar to iCal or googleCal.
Is there a way this can be done with out a VBA, my work has restricted access to VBA content.
For example can you take a month, January, and then show all the scheduled appointments for January just like the example above? No need to click on the date in the calendar to see appointments, just a month at a glance? With the highlighted calendar above it?