When I find I am performing the same task repeatedly, I look for ways to make my life easier. I prefer spending my time doing new and exciting things, not monotonous and redundant activities. Often, these experiences give me the chance to try something that will save time, and keep me entertained.
One example of this is a recent monthly report I am responsible for maintaining. It has sales numbers for the present year – Year to Date, as well as the previous year. I can change the month of my report in one place using a dropdown menu. This dropdown menu is referenced by any place in the file where it looks for current month.
To do this:
- First I enter a list of all the months of the year into 12 cells
- I then choose where I want to add my dropdown list by selecting a cell
- Then I select the Data tab from the ribbon
- Next, I choose Data Validation from that menu (screenshot above)
- A Data Validation popup menu then gives some additional options – Choose list from the dropdown menu
4. Then the prompt asks to select data. I select the list of months from step 1
Now I have my dropdown box. I can always reference cell D3 whenever I want to populate the current month I want to pull data for. This part was fun to do, but the next part will make life even easier.