Show/hide rows based on value entered at top of column based on single or multiple values in column

AgentKMK

New Member
Joined
Jul 22, 2018
Messages
9
VBA noob so any help is much appreciated as I have tried but failed to do this. I am using the latest version of Excel.

I need to be able to show/hide project rows depending on the value a user enters into a cell from a drop-down list in row 2 at the top of column in question. My sheet has a header row (row 1), followed by row 2 where the sort value is entered. The data starts at row 3 and ends at row 114. The rows are projects.


Here is a small snapshot.
https://www.dropbox.com/s/dpbha293ll3i853/PM Sheet Example.png?dl=0

1. Project Status (Column H)
Projects move through statuses as work progresses from start to finish. The status is selected from a data validation dropdown list from a range named Proj_Status. Although I may be adding more statuses in the future, the current statuses are: IRC, WCW, WVE, RTB, WMB, RTR, REV, MYPL, DONE, CANC. This has been applied to range: H3:H114.

I have applied a different data validation dropdown list to cell H2 named Proj_Stat_Sort. The current statuses are IRC, WCW, WVE, RTB, WMB, RTR, REV, MYPL, DONE, CANC, BLANK, ACTIVE, REVIEW.

If cell H2 is left empty, all project rows should be shown.

If the user selects a certain project status in cell H2, only those projects occupying that status should be shown. For example, if they select REV then only REV project rows should be displayed.

If the user selects BLANK in cell H2, only those projects with no data in the project status field should be shown.

If the user selects ACTIVE in cell H2, projects with the status DONE or CANC should be hidden.

If the user selects REVIEW in cell H2, only those projects with the status RTR or REV should be shown.

2. Team Member To Do (Column J)
Team members have their initials added in this column. As multiple team members can work on a project, projects can have more than one set of initials, separated by a comma.

How do I create a data validation list so the user can select from a dropdown range of initials, but where the cell can have more than one set of initials separated by a comma? Is that possible? The options, in any combination, are: MS, CH, KK, EE, JB, DT, NM, BN, IF. These initials are held in a range named Team_Member. This validation would be applied to the range: J2:J114.

In the search cell at the top of the column (J2), if a users selects a certain team member’s initials (one only), only those projects with their initials would be shown.

Thanks a million.

AgentKMK.
 
Hi Fluff - where is the VBA code meant to save? When i made the button it automatically saved it as a module rather than directly in the sheet.
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
With shapes/form controls the code should be in a regular module.
ActiveX controls need the code in the sheet module, but you cannot use ActiveX on a Mac
 
Upvote 0
Hi - I am going to post the code you gave me in the forum as a new question to see if anyone else knows how to get it to work in Excel for Mac as well as Windows.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top