Hiding/Filtering Rows Automatically based on a Value

Question

Hi everyone

Looking for some advice on automating a portion of the checklist I'm currently making.

Basically, the idea is that people fill out the spec list for each assignment, and from there items in the checklist are marked as need or not.

While there are other functions going on basically if an item is marked as No on one page the task gets an N in the "Applicable" column. I used conditional formatting to grey out the tasks that weren't needed but is there any way to have it so that any tasks that get an N in the "Aplicable" column will automatically be hidden (as in the entire row)?

Also as this will be used on several assignments and change would occur over time, can it be set to hide/unhide these items as their status is updated.

I`ve tried playing with conditional formating and using VBA code (although I have little experience with it) but can't seem to get something that both hides and unhides as I want it to, and updates based on any changes.

I tried to attach a rough sheet for reference, that has the items that are relevant to this, but seems to not let me so have attached the screenshots,

Would anyone be able to assist me on getting this to work?

Answer 1

Hi @Marcus Rowe

In this zip file a .xlsm that does what you want. Note that this is very quick work, you'll have to adapt and polish it

I used Tables instead of ranges as this offers much more flexibility. Tables for Phase1...Phase3 and respectively named Phase1, Phase2 and Phase3. The table for the required colors is named tblReq

Answer 2

@Lz._

Thank you for the sheet, I had a play around and adapted the code to my setup, it's a little larger so phases went to 10, etc.

However, it does not seem to be working after my tinkering.

Looking at the code I think the issue is in the fact that my Yes/No checklist table is on another sheet (and is 2 tables).
Would you be able to offer any insight into how to modify this part of the code to this situation?

Or is it possible to just be really simple and have if column K has "N" then hide that row. And have it check and hide/unhide if changes are made to column K?

Thanks

Answer 3

@Lz._

The Yes/No list was split into two different tables as they are two quite different things, and I was asked to have it so they could be checked separately.

It will take some time to make a dummy list there is a lot to delete from the file before it can be shared.

I'm going to also try and move things onto one table on the second sheet, would you be able to offer advice and how to adapt things assuming that it's one table(A1=Items B1=Needed) on sheet 2?