Advance Filter Button

[blacky1]

We have a workbook with 3 sheet. In the “On Air Notice” sheet we have a selection (In Green cell C5) that you can select a switch board and it will display the information relating to that switchboard from another sheet. Now we would like to put in a button on the “ON Air Notice” sheet that will filter out a few things.

When the button is pressed we would like it to do the following;
1/Look in “A” column table from row 8 to infinity and if “A” is displayed use/display this information. If it is anything else in the “A” column from row 8 to infinity then hide this information.
2/ Hide all duplicated items but display 1 of the duplicate item.

Once we push the button and it runs the filter we can now look and print this information but if we select another switchboard (In Green cell C5) it automatically clears the button filter. Or we could just have a clear filter button.

Is this possible? Could someone help?

I have attached our workbook to make it easier to understand.

Commissioning Test_4.xlsm

Thanks

CB

[Mallycat]

Everything is possible. Before offering to help, let me check something. If you don't want to see all this other data, why do you bring it into the table in the first place? Why not filter out the data you don't want when you fill the table?

Edit: plus I can't find any code in your sheet. Where is it?

[blacky1]

Hi Mallycat, I did think of this but couldn’t get it to work. If you could help that would be great. Attached in a normal excel workbook. The code we use to pull data from the "FIRE MODE" sheet is in 2 places. 1st is in the cell in the table "On Air Notice" and the reference is in the "Fire Mode" sheet column "P" as a reference. If we could get this code to check for “A” in the “Fire Mode” sheet “C” column and only bring in this information to “On Air Notice” that would work and then we could just use the advance filter “Unique Records Only” to do the rest. If we can get the existing code to bring this information into the “Air On Notice” we can make the button for the filter…… but not sure how to get the filter to clear when selecting another switchboard from the “On Air Notice” automatically but failing that we could create a clear filter button.

Commissioning Test_4.1.xlsx

R

CB

[blacky1]

HI Mallycat.... or if possible if we can bring the information from "Fire Mode" sheet into the "On Air Notice" doing the all the filters that would work to. Would like to know how so we can use this in the future.

R

CB

[Mallycat]

OK, now I see how you are doing it. Man this is a complex spreadsheet. What is your apetite is for simplifying it and starting again? This is what I would do.

1. turn your data on FIRE MODE into a proper table. Proper means... give every column a unique heading in a single cell, then use the command INSERT\TABLE and select "my table has headers". What this will do is help manage the data for you. At the moment, you have preformatted the conditional formatting in this table to almost row 1,000. One thing that a proper Excel table will do for your is manage the conditional formatting as you add extra lines.
2. Set up an advanced filter to pull your data from FIRE MODE into ON AIR NOTICE. I have set one up for you on a new sheet and created a simple macro to run the extract. The key things to know about these filters is that you can use any table heading form your source data as the criteria (Red) and any of the column headings to be the extracted range (Blue).

Now I don't really understand your data, but would this approach be better than all those formula you have?

[blacky1]

Hi Mallycat, wow!!.... I love it!!.... really got me thinking down a different path from now on...... you have changed my way of thinking and can't thank you enough...... this is perfect…. now to fix my data base…. Thanks Again!!

R

CB

[blacky1]

Hi Mallycat, we have edit our workbook but we have found one problem that you may be able to help us with. When we select the Number table(1 to 5) the extracted table bigger in 5 than 1 and doesn’t retract. Is ther a way to fix this? Also if we don't what the colour (Light Green) to be coped over is there a way to hide that? See attached file.

Commissioning Test_4.2.xlsm

R

CB

[Mallycat]

I am glad you like the suggested approach. Sometimes it is simply a case of 'you don't know what you don't know'. That's why I tend not to automatically give people what they ask for in this forum, particularly if I suspect something is not right.

The table that you describe being left behind after extracting number 5 is related to the residual formatting on your source sheet. You actually have a stack of conditional formats that I have now deleted. So now the only formating on the source sheet is the table formatting. Table formatting will not get copied across when you do an extract.

Regarding the Green colour, there are 2 ways. Either delete the colour from the source (easiest) or reformat the table after the extract is finished. If you want to do the latter, let me know and I will give you some code. I guess it depends on what you are trying to do with the extracted data. As it is now (see attached) there is no formatting on the extracted data

[blacky1]

Oh okay.... Thanks for your help again .

R

CB