Dynamic dropdowns are a handy way to get your users to make choices based on what they’ve previously chosen, while steering them away from making invalid choices. Today we’re going to look at one that easily handles multiple levels, and we’ll take a look at what could go wrong. Let’s see one in action, shall we?
Right, what’s on the (dropdown) menu?
Fruit, anyone?
*BING!*
Cool…check it out…as you can see from the above, the user gets prompted with “Choose…” whenever a subsequent choice must be made.
Ok, what kind of fruit should I have? Hmmm, let me see….eeny, meeny, miny, STRAWBERRIES!!!…MO!
*BING!*
Ok, so what delights does Sub Category 2 have in store for me?
Earliglow? Never heard of it. Sounds delicious…I’ll have those, please.
*BING!*
There, all done. Pretty nifty eh…users only get to see valid choices depending on what they chose last. So users simply can’t screw up! Or can they?
[Evil user, determined to prove me wrong]: Wait a minute…I just remembered that mother expects me to eat my vegetables first, before I move on to dessert. So I better change that initial selection:
*BING!*
What the…Strawberries are vegetables???
Damn…changing upstream dropdowns later on means those downstream choices can be flat out wrong! So how can we make this bulletproof?
Macros to the rescue
Yep, we’ll use some code to clear out any ‘downstream’ choices if anything ‘upstream’ changes. Let’s go back to that original strawberry fest:
Now watch what happens when our user subsequently decides they better vege out first:
*BING!*
Ahh…look at that: the code realized that all those downstream choices are no longer valid. So it deleted them, and prompted the user to choose again. There. Now that IS bulletproof.
So let’s see…hmmm…for an appetizer, I’ll have baby carrots:
And I already decided on Strawberries for pudding…
But what about my main course. Ah, yes, of course…
MEAT! Yummy. BURP!
What’s the recipe?
My approach draws on Roger Govier’s excellent sample file on the Contextures website. Be sure to check out that link to see Roger’s in-depth discussion of the formula magic behind this puppy…It’s genius.
In my Dynamic-Dependent-dropdowns-20140214, you’ll see that all the different categories used by the dropdowns are hosted in an Excel Table, that has the initial categories down the left hand side, and subsequent categories across the top:
So how do these categories get used by the data validation dropdowns? Roger’s approach uses two dynamic named ranges to feed the data validation lists, one called MainList and one called SubList:
Here’s the MainList formula:
=INDEX(Table1[[Choose…]],1):INDEX(Table1[[Choose…]],COUNTA(Table1[[Choose…]]))
…and here’s the SubList formula:
=IF(OR(Sheet1!B8="Choose…",Sheet1!B8=""),"",INDEX(Table1,1,MATCH(Sheet1!B8,Table1[#Headers],0)):INDEX(
Table1,COUNTA(INDEX(Table1,,MATCH(Sheet1!B8,Table1[#Headers],0))),MATCH(Sheet1!B8,Table1[#Headers],0)))
The SubList formula has a relative reference in it: whatever cell you use it in, it retrieves the value of the cell to the immediate left, and then it scans the column headers of our validations table (Table1) looking for the heading that matches that value. Once it’s found it, it simply uses the items listed underneath that heading.
Because this formula is relative, before you enter it into the Name Manager, you will need to first select cell C8, because the above relative formula refers to B8 – the cell to the left. (Note that it doesn’t matter what is in C8 or where your actual dropdown are…rather it’s just that the above formula happens to refer to B8, and because we want our formula to always reference the cell on the immediate left, then we’ve got to select the cell to the immediate right before we enter this relative formula into the Name Manager.
Also note that my version of Roger’s approach uses Excel Tables and the associated Structured References that Table functionality allows. My table is called Table1. Your validation lists MUST be held within an Excel Table (which requires Excel 2007 or greater) and you MUST change the Table1 references in the above formula to match the name of your table.
Excel Tables – known as ListObjects to VBA developers – were introduced in Excel 2007, and are a very powerful and simple way to store things like lists, chart data, and PivotTable data…especially if you might need to add more data to your spreadsheet at a later date, and want to avoid having to repoint all your formulas to include the additional data. If you’re not familiar with Excel Tables – or you don’t know what that Table1[#Headers] guff above means – then I strongly suggest you check out Chandoo’s Introduction to Structural References and this great video he did with MrExcel.
The way these two formulas work is very clever. That MainList named range only gets used by dropdowns in that very first ‘Main Category’ column:
…and all other ‘downstream’ dropdowns – no matter what level they are – are fed by the SubList named range:
The beauty of Roger’s approach is that it can handle any number of cascading levels, provided all the category names are unique. All you need to do is simply add the new subcategories to the right hand side of our validations table (Table1).
Let’s look at an example. If you look at the below screenshot, you’ll see that users can choose from a number of different kinds of meat:
Let’s add a further level that would give meat eaters some further choices relating to how their meat is prepared.
To set this up, all we need to do is take the individual items from that ‘Meat’ column and add each one as a new column header:
Then we simply list the new options for each type of meat below the relevant header:
Now here’s the magic: as soon as we add another column to our input table and set it up with data validation – which I did simply by clicking on the bottom right corner of the cell with the word ‘Human’ and dragging it across – then Excel picks up on the fact that there’s a sub-subcategory, and serves it up to us. *BING!* Order up!
Add code, and stir-fry for 10 milliseconds
As mentioned earlier, in addition to Roger’s great method, I’ve written some code that clears out any downstream entries in the event that an upstream entry is changed. It’s in the sample workbook already, all set to go. But here’s the actual code, for you VBA nerds. (Special thanks to Gabor Madacs for some enhancement suggestions)
Option Explicit
Const CHOOSE = "Choose…"
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Dim targetCell As Range
Dim nextCell As Range
Dim oldCalc As Excel.XlCalculation
If Not Intersect(Target, [DataEntryTable]) Is Nothing Then
If [Radio_Choice] = 1 Then
With Application
.EnableEvents = False
.ScreenUpdating = False
oldCalc = .Calculation
.Calculation = xlCalculationManual
End With
For Each targetCell In Target
'Clear any cells that use 'SubList' to the right of targetCell in the current table.
If targetCell.Column < (targetCell.ListObject.ListColumns.Count + targetCell.ListObject.Range.Column - 1) Then 'there are table cells to the right
For Each nextCell In targetCell.Offset(, 1).Resize(, targetCell.ListObject.ListColumns.Count + targetCell.ListObject.Range.Column - targetCell.Column - 1)
If HasValidationFormula(nextCell) Then
If nextCell.Validation.Formula1 = "=SubList" Then nextCell.Value = ""
End If
Next nextCell
End If
'Perform different action depeding on whether we're dealing with a 'MainList' dropdown
' or a 'SubList' dropdown
If HasValidationFormula(targetCell) Then
Select Case targetCell.Validation.Formula1
Case "=MainList"
If targetCell.Value = "" Then
targetCell.Value = CHOOSE
ElseIf targetCell.Value = CHOOSE Then
'Do nothing.
Else
targetCell.Offset(, 1).Value = CHOOSE
End If
Case "=SubList"
If targetCell.Value = "" Then
targetCell.Value = CHOOSE
ElseIf targetCell.Offset(, -1).Value = CHOOSE Then
targetCell.Value = ""
ElseIf targetCell.Value = CHOOSE Then
'Do nothing
Else
Set nextCell = targetCell.Offset(, 1)
If HasValidationFormula(nextCell) Then
If nextCell.Validation.Formula1 = "=SubList" Then nextCell.Value = CHOOSE
End If
End If
End Select
End If
Next targetCell
With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = oldCalc
End With
End If
End If
Exit Sub
ErrorHandler:
With Application
.EnableEvents = True
.ScreenUpdating = True
If oldCalc <> 0 Then .Calculation = oldCalc
End With
MsgBox Err.Description, vbCritical, Name & ".Worksheet_Change()"
End Sub
Private Function HasValidationFormula(cell As Range) As Boolean
On Error GoTo ValidationNotExistsError
If cell.Validation.Formula1 <> "" Then
HasValidationFormula = True
Else
HasValidationFormula = False
End If
Exit Function
ValidationNotExistsError:
HasValidationFormula = False
End Function
Hungry for more?
Here’s some related Posts at Chandoo.org:
Download the file
To see how this is done, download this file and enable macros:
Dynamic-Dependent-dropdowns-20140214
About the Author.
Jeff Weir – a local of Galactic North up there in Windy Wellington, New Zealand – is more volatile than INDIRECT and more random than RAND. In fact, his state of mind can be pretty much summed up by this:
=NOT(EVEN(PROPER(OR(RIGHT(TODAY())))))
That’s right, pure #VALUE!
Find out more at http:www.heavydutydecisions.co.nz
49 Responses to “Interactive Pivot Table Calendar & Chart in Excel!”
Excellent post again from awesome chandoo.org
This is one of the post to evident, without using macros we can create excellent charts using available excel options.
Slicer is one of the useful option in excel 2010 .. excited to see more options in excel 2013.
Regards,
Saran
http://www.lostinexcel.blogspot.com
Nice one chandoo............... great work done.....
Cool article. Only downside was that I didn't see at first that I needed 2010. Guess I still have to wait awhile before getting to try this out myself.
I consider myself an Excel expert, but you constantly amaze me with posts like this. Fantastic calendar!
Good post, like this little trick!!
How to not show the value in the cell
format the cell to custom with the below
;;;
Could you add lists of holidays to be transferred to the calendar days?
Two lists would be needed: 1) for the holidays that stay fixed (eg, CHristmas), and 2) for the holidays that move around (eg, Thanksgiving).
Such lists would be prepared externally, and the program would transfer their information to the appropriate days.
Wow! This is something amazing. I am going to do some practicals with this and show a sales trend on this. As we have our sales plans weekly basis, this should impress by boss when put in dashboard. Cool.
And thanks1
Chandoo you have a knack of getting on to these great looking very creative ideas!
One thing with calendars I have seen before is not catering for able to enter notes or appointments or project milestones. But with this one it's easy enough to add the extra lines as you have done for the chart concept and link to this other type of info.
For 2003 we could replace slicers with a validation style dropdown couldn't we?
Chandoo, you are awesome;) i was using calender to show my reports, but i had made all months and then underneith date shows the value, man its really awesome . i am going to use this format for my reports.. only draw back for me is i am using 2007. hence no slicer.. may be have to modify with out slicer.
Why not use =weeknum() for the weeknum column?
Great tricks! I love trying to reproduce the charts myself to get the hang of 'em. This one was great.
My only issue is getting the VBA in the year object to refresh the data. I used the VBA provided at the link, and, I can see it in the Macros tab, but, when I click the spinner the data does not update. Any tips?
Thx!
3G
^^Ignore this! NOT ENOUGH COFFEE YET.
I forgot about the "Assign Macro" option
:-s
Just started at chandoo - this is great!
I opted to use the formula =IF(F6>F5,G5,G5+1) for my weeknum - worked for me (I didn't get all the way through the example, since I'm running Excel 2007 - so don't know if that'll affect anything later in the example). I'm open to comments on this alternative approach.
Thanks for creating this website!
VC (Excel student).
Very cool - but now I'm even more excited for the new time controls for Excel 2013!
Great calendar...
I wonder whether we can make a school calendar (Class, subjects, teachers) using this calendar, assuming the weekly plan is duplicated across the year.
I would love to be a part of creating a class schedule...I'm attempting to help a friend (gratis) to do just that - can you point me in the right direction or provide a sample of sorts?
[...] Wow – what do you think of the interactive calendar chart demo above? To achieve this impressive effect you must have Excel 2010 because it utilises slicers, which is a feature introduced in Excel 2010. Find out how this treasure was created on Chandoo’s page. [...]
Hello Chandoo,
Great works! I learn a lot from this website. Here is the problem I met when I follow your tutorial: once I run and save this cool pivot calendar chart , the size of excel file will increase every time. Could you let me know how to figure it out? Thank you for your time in advance.
An excel chart-fan from China.
I already figured it out.
wow, love the calendar, i'm a newbie, found this site and it's amazing.
Got it mostly figured out, but could do with help with your named range 'tblchosen'
I can build the pivots, link the calendars together but can't see how to use index(tblchosen...) to pull through the productivity figures
appreciate any help
thanks
Great. Miss the Today button. Will try and figure a way to add this to the file.
I want to start the week on Monday, not Sunday (MTWTFSS). Re-arranging the calendar tab works however, any month where the 1st is a Sunday starts on the second and totally omits Sun 1. I have been tinkerign for a while, but can't seem to figure this out.
Changing F2 on the 'Calcs' tab to 2 so that the week starts on Monday works.
Cutting & pasting Sunday on the 'Pivot Calendar' tab and moving all cells up 1 row works.
However, using April 2013 for example, you lose the 1st off of the pivot calendar so that the month starts on 2 April. What should happen is the first row should only show Sun 1 April and then the next row starts Mon 2 April. Still can't fugure out where the problem lies.
"Further Enhancements:
Adjust week start to Monday: Likewise, you can modify your formulas to adjust weekstart to Monday or any other day you fancy."
I have tinkered with this previously with no success, does anyone know which formulas require tinkering, I have only succeeded in breaking this in an effort start a week on a Monday.
[...] Interactivo Artículo original var dd_offset_from_content = 50; var dd_top_offset_from_content = 0; Tags: 2013, calendario, [...]
Completely off topic, but how do you create those animated pictures in your tutorials? It is not a movie (like the Youtube movie), so what software do you use to create such high quality "animated" pictires? Thanks
Jeroen
the animated pics are called Animated Gifs
they are made using Camtasia
Refer: http://chandoo.org/wp/about/what-we-use/
This is fairly easy to do just using calendar formulas, which would be quicker, and doesn't need VBA? Am I missing something?
[...] on how to generate an interactive calendar using pivot tables. Please check out Chandoo’s Interactive Pivot Table Calendar & Chart in Excel before reading this, as I want to go through how I used his method to adapt a calendar which was [...]
Great tip shared by you... howevr would appreciate if you could mention in your tricks about excel version. The example above would work only in excel 2010 and above I believe. Please help me if there is any way we can use the tip in excel 2007 as well..
Many Thanks,
Regards,
FK
Hi, I'm going to give this a shot, but one small question before I do. Can a linked cell be updated based on the date that is selected from the calendar? The calendar is really cool and this would make is especially good to use (and easy and fast).
Regards,
swissfish.
This post is awesome, and using your instructions, I was able to get this to work with a pivot table that pulls directly from a Project Server database. It was a bit complicated to get the day to sum correctly, but I managed to finagle it. I hope you don't mind if I link back to you when I post my instructions.
Thanks for giving me a starting point for this!
[...] http://chandoo.org/wp/2012/09/12/interactive-pivot-calendar/ [...]
This is great, and pretty much everything I was looking for.
However, I already have a large spreadsheet, and I want to include your worksheets in it. I copied all the worksheets and the Module 1, but I can't get it to work. What else do I need to transfer / update please?
Hello there, is it possible to use this pivot to produce a calendar style chart, with returns multiple data per date, which on the calendar then, when clicked links to the data to provide more background information? What do you think? I'd love if I could pivot when i need. thanks, m
Hi, did you ever figure out how to do this? I would love to find a way...
This is amazing and will work well for my calendar project! My question is, how can I expand the calendar to fit a standard sheet of paper?
Wow - this is so creative. I'm taking the basic idea and building a reservation calendar.
Question: How do you get the month and year slicers on a different page than the pivot tables? I'd like to have my final calendar on a separate page from the pivot.
[…] http://chandoo.org/wp/2012/09/12/interactive-pivot-calendar/ […]
This is perfect...is there a way to add notes/tasks to the individual days?
Excel will not let me insert blank rows between lines in the pivot table. I am use Excel 2013 - is there a pivot table tools command that must be used?
I can create the pivot table calender with a year spinner & month slicer but I do not see how to display the the attendance information that I have in the original data table.
Thank you for the wonderful post and I am sorry for my lack of understanding...
Excellent!
Please show me how to add an alternative calendar to this calendar, Chinese or lunar calendar (and by lunar I don't mean phases of the moon), like what they still use in Asia
Thanks
Christopher
[…] Wow – what do you think of the interactive calendar chart demo above? To achieve this impressive effect you must have Excel 2010 because it utilises slicers, which is a feature introduced in Excel 2010. Find out how this treasure was created on Chandoo’s page. […]
Hello my name is Maurice, excuse me for my further request, but believe me, without your help priprio not know how to solve this problem.
So: always using a chart positioned on an excel sheet I wanted to match each square (series) to a single cell, to create a perpetual calendar.
Now everything works fine; except that for a fact, and it is this: In the calendar as you well know some numbers may not be apparent until certain conditions, which I solved by writing this "= O code (AA5 = DATE ( $ H $ 1; MONTH ($ AD $ 12) +1; 1)) and the game and done.
Now I would like to achieve the same thing using the Chart; How can I do to make this happen! let me also just a practical example so that I can understand all the rest then I'll do; Thanks Greetings from A.Maurizio
Link Program : Link: https://app.box.com/s/lhqva3eji0xcf2nmk8lxyki88tt1mi5t
Great info, thanks for sharing
Hi,
I love your calendar however I am modifying it for use in displaying employee performance metrics on a day by day basis.
I see where tblChosen and tblDates are named ranges however I cannot find them anywhere.
Are they assigned to specific cells because I cannot tell.
I see both of them in the Name Manager, which tells me what they refer to but does not give a value or cell location.
@Mike
With the Names in the Name Manager
Simply select the name
Then click in the Refers To: box at the Bottom
Excel will take you to where the Named Range is referring to
[…] Wow – what do you think of the interactive calendar chart demo above? To achieve this impressive effect you must have Excel 2010 because it utilises slicers, which is a feature introduced in Excel 2010. Find out how this treasure was created on Chandoo’s page. […]
Hi, Chandoo
This Pivot Calendar is an excellent idea. I’ve done one for myself using your guidelines. I just need something I’m not being able to do. I need that when I open the file the default date is set to today’s date. I know how to do it with conditional formatting. But I think I’ll need some vba coding for this. Can you please help me with this. Thanks in advance