Share via

HOW DO I SHOW/HIDE ROWS BASED ON A DROP DOWN BOX

Anonymous
2025-01-09T14:07:55+00:00

Excel 2016 (company uses old versions of MS products)

I have a table with column headers (event 4D, month 4E, and remarks 4F) and I would like to create a dropdown box next to it (4B) that can do the below things:

  • shows the entire data set (All)
  • only shows the rows of month I select (May, June, July, etc.) and hides the others, and
  • only shows the rows of months within the quarter I select (1st, 2nd, etc.) and hides the others

Example below.

I keep seeing =UNIQUE or macros in forums and YouTube, but =UNIQUE doesn't show up in Excel 16 and the VBA Macros are throwing me off. What is the best way to accomplish this?

Dropdown box goes here Event Name Month Remarks
Milkduds January remark
Bilbo Baggins February test
Riboflavin March example
Lettin' Gas April trial
Moist Mildew February help me!

Thank you for your support.

  • Christopher
Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points
    2025-01-15T11:12:02+00:00

    This setup is not really suitable for what you want. I'd use the filter drop-down in B4 and/or C4.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2025-01-15T09:44:10+00:00

    Here you go:

    https://www.dropbox.com/scl/fi/rbudq42sc16tjbpk2fh18/Event-calendar-sample.xlsm?rlkey=h0up33285c3rhmg4qs7stsfyh&st=lkamp6bp&dl=0

    Sheet1 is based off of what you originally sent, I didn't touch sheet2, and sheet3 is what my boss likes the final version to look like.

    Let me know if you have an issue with opening it. Thank you.

    Was this answer helpful?

    0 comments
  3. HansV 462.6K Reputation points
    2025-01-14T16:09:48+00:00

    Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2025-01-14T14:31:27+00:00

    HansV,

    This worked very well and I love it, now my boss is looking for something more robust. I was able to data fill it with appropriate columns and rows as per his direction, but a few things I’m trying to do seem to not jive with the code:

    • Once I started putting data in, I could not move the “month” from column D, and I could not move the dropdown box from row 4.  I also had to be careful when putting columns next to them.  I assume that’s a result of it being macros and they need to stay put.
    • I wanted to add conditional formatting into a remarks column, but it would not work at all.  It would work in a regular excel sheet, but not this one.
    • I wanted to add a legend for color code conditional formatting underneath the dropdown box, but it would be a part of the rows in the table and would vanish when sorted. I had to put it at the top.
    • I tried to change the content and context of column D, but it broke the dropdown every time.

    Per the below example, what I’m trying to do according to what the boss wants:

    • Still keep the awesome dropdown but in cell B2, and sort via column B (start date):
      • shows the entire data set (All)
      • only shows the rows of months per year I select (January 2025, June 2025, September 2026, etc.) and hides the others, and
      • only shows the rows of months within the quarter and years I select (1st 2025, 2nd 2026, etc.) and hides the others
      • This will currently be from 2025-2027; how can we can update the years as we move forward in time? I assume I'll need to adjust both sheet2 and the code.
    • They want the each row to change color based on if the verbiage in the legend shows up in column K (remarks). Example: if it's a holiday and we type "this is a day-off holiday" in the remark, it makes the entire row red as per the legend. I know how to do conditional formatting, but it will not work with this for some reason.
    A B C D E F G H I J K
    1 ACTIVITY TRACKER - 2025-2027
    2 AT A GLANCE - click dropdown box ----> All ACTIVITY LEGEND: NO ACTION (LIGHT GREEN) ACTION CONFIRMED (DARK GREEN) HOLIDAY (RED) EVENT TBC (PINK) ALT EVENT TBC (LIGHT BLUE) ALT EVENT CONFIRMED (DARK BLUE) ALT EVENT (PURPLE) TASKING (ORANGE)
    3 EVENT INFORMATION TYPE OF EVENT
    4 EVENT NAME START DATE END DATE TRACKER # INST MSC EX WG CONFERENCE VISIT REMARKS
    5 NEW YEARS DAY 01 January 2025 01 January 2025 this is a day-off holiday
    6 Mtg Prep 06 January 2025 10 January 2025 No action
    7 INpro int 09 December 2025 09 December 2025 Action confirmed
    8 IN-processing 13 May 2026 07 Jun 2026 Tasking

    I really appreciate any additional support you can provide. I think I can do it myself once we upgrade to Office 365; just some more help in the meantime.

    • Christopher

    Was this answer helpful?

    0 comments
  5. HansV 462.6K Reputation points
    2025-01-09T15:13:41+00:00

    This requires VBA code, so it will work only in the desktop version of Excel, and you will have to allow macros.

    See the demo workbook FilterTable.xlsm

    To view the code, right-click the sheet tab of Sheet1 and select 'View Code' from the context menu.

    Was this answer helpful?

    0 comments