This video will outline advanced drop-down lists in Excel.
Here we have a financial analysis of sales by month.
Now let’s say we want to create a drop-down list of this month range.
Further, we want a start and end month.
Naturally, we want our 2 drop-down lists to be interrelated;
that is the second or end month drop-down list
will be influenced by the input of the first or start month drop-down list –
and vice versa.
For now, let’s create simple drop-down lists using data validation.
If our start month is January, then our end month must be February or later.
Go to cell H5 and type the following:
=IFERROR(MATCH(F5,$B$6:$B$17,0),0)
Clearly January is the first month in this range,
and February is the second month in this example.
These formulas are pivotal to the success of this exercise, hence their accuracy is paramount.
Now, we will write a formula to identify the position of the end month – relative to the range.
Ensure to type this formula:
=COUNTA(B6:B17)- IFERROR(MATCH(F6,B6:B17,0),0)
This formula acts as a bottom up counter of the end month value
February’s value of 10 means it is 10 places above the last month in the range –
December.
We are now ready to create our defined name for our end month in cell F6
Given the complexity of these defined name formulas, I will type them in column K, in order to illustrate the key elements.
Type the following formula:
=OFFSET(Sales!$B$6,Sales!$H$5,0,COUNTA(Sales!$B$6:$B$17)-Sales!$H$5)
Ensure to reference firstly cell B6 –
the first value in the range.
Then the count value of start month in cell H5.
Columns will be zero.
Height will determined by the number of months in the range using COUNTA
less the count value in cell H5
We need to prevent users selecting the start month in our end month list.
Copy this formula, select Formulas >
Name Manager >
New
I am using the name
endMonth.
Under Refers, paste the formula from cell K6
and click OK
We will now write the formula for the startMonth.
Type this formula in cell K5:
=OFFSET(Sales!$B$6,0,0,COUNTA(Sales!$B$6:$B$17)-(Sales!$I$6+1))
Again, ensure to reference cell B6 – the first cell in the range
However both rows and columns are zero.
Height
uses this COUNTA formula
less brackets,
the value in cell I6,
which is the end month position from Dec 2016.
Plus one.
Close brackets,
close brackets.
Copy this formula.
Go to Formulas >
Name Manager >
New
Paste this formula from cell K5 under Refers To.
We can now assign the defined name to the drop-down list.
Under Data Validation >
Source,
change the range to startMonth.
Do the same for the second drop down list in cell F6 – but change the source to endMonth.
We can see with the endMonth drop-down list, we can only select months after January 2016 – because this is the value selected in cell F5.
IF we change the end month to March, and click on our start month drop-down list,
we can only choose the earlier months of January and February.
In summary, here are the two formulas that are used in the defined names of startMonth and endMonth respectively.
As a final check, I will select December as my end month
When I click on the start month, this drop-down list now extends down to November 2016.
This highlights the use of defined names for advanced drop-down lists in Excel.