Trying to create a drop down list that is able to filter out all other columns base on selection (calendar months)

Question

I have a rather crude spreadsheet that tracks simple monthly data with each month in a different column. I'm hoping to create a drop down list in cell B6 or C6 which allows the user to select the appropriate month and it will filter out all of the other columns/months so they don't have to scroll across from left to right. The 'Quarterly' headers are not 'Merge and Centre' they're just 'Centre across selection' (if that makes any difference).

Thanks

Answer 1

This cannot be done with formulas. You will need VBA to achieve that.

Conceptually: Create the dropdown in your desired cell for the month number.

Add code for a Worksheet_Change event that does the following:

• check that the change was the dropdown cell. If not, end the macro.
• Hide columns F to the last column
• use a Select Case statement with cases from 1 to 12
• depending on the selected month number, unhide the columns that pertain to that number

You can use the macro recorder to help with the basic syntax and fine tune it to your needs.

With VBA in the mix, you will need to save the file as a macro-enabled workbook with the .xlsm extension.