I have a spreadsheet that I want to filter between "Designer" and "Engineer".  Under each one I have listed tasks for either group to do, the problem is I have merged cells under both "Designer" and "Engineer" that contain blanks.  In order to  filter either "Designer" or "Engineer" to get all of their tasks to show up on the spreadsheet I have to click the "Blank" box in the filter drop-down.  Being that both "Designer" and "Engineer" tasks include blanks I am unable to filter just "Designer" or just "Engineer" because when I click the blank box in the filter checklist all of the blank rows show up for both "Designer" and "Engineer".  I know this sounds confusing, but my question is....Is there a way to make 2 custom filter, one "Designer" and one "Engineer" and make it where it only shows the blanks for that specific filter ("Designer" or just "Engineer")?

Maybe this example will help clarify what I am trying to filter.  Excel shows both "Designer" and "Engineer" when I check the blank box in filter. 
The "Electrical Contractor" cell is merged, as well as the "Designer" cell, "3" and "One-Line".

 Electrical Contractor  Designer  3  One-Line Xref'd, attached or inserted image files are loaded and correct. all spelling is correct. text is consistent: same size and same font style. all matchlines have correct reference to drawings. item callouts and tags are consistent. voltages and main circuit breakers correct and called out correctly. all motors and loads are correct and called out correctly. future/spare gear is indicated on  drawing and easily identifiable. Electrical Contractor Engineer X One Line Verify UPS size, voltage and number of phases and plan makes sense.  Verify voltages, kVA on transformers & panel schedules makes sense. Single phase vs. 3 phase Verify scheme will accomplish arc flash ratings that the customer desires. Verify mains vs. MLO needed. Is there a high resistance ground? Not if it is a utility transformer or surge protection?  Add note to remove bonding jumping between XO and ground for resistance ground. Is there surge protection on incoming gear? (not with HRG system) Does percentage of 480 V VFDs require harmonic mitigation ? Active filter or passive filters on VFDs Does the customer want a power quality meter? Should Power Factor Correction be considered for the site?


Can I use a formula to make a special filter?
Advanced filtering doesn't help me.
MelindaB (rep: 2) Nov 7, '18 at 6:09 pm
Add to Discussion


Selected Answer

What makes Excel such a great tool is its capability to roll database, data entry, data evaluation and data presentation into one. Users don't have to learn the basics. However, just because Excel can do that doesn't mean that is the best way of doing it. The best way of doing a job isn't - and can't be - not to learn the basics.

With that said, my suggestion is not to filter. Your worksheet, obviously, is designed to present the data, either for the Electrical engineer or the Designer. So, where are the data? The data must be in a place where they are easily entered and modified. It is important that the database has no merged cells. However, once you design it as a database that bit usually comes by itself.

Then you create two worksheets, one for the engineer, the other for the designer. Both draw on the database, so that when a change occurs in the DB it is reflected in both sheets (or in the appropriate sheet).

Your problem therefore isn't one of filtering data (because you don't have a database) it is one of workbook design. You should separate data base from data presentation. Usually, in Excel, data entry (and data modification) goes with the database. I suspect that your work flow intends data to be modified in the filtered presentation sheets. That can be done too, but at significant additional effort. If your DB is so complicated that it needs a form for the user to enter data correctly, consider creating a form to enter and modify data correctly. Bear in mind that it is the multi-purposing - using the same form for entry, modification and presentation - which is creating problems.

The final point I want to make here is that work flow and workbook design are closely related. If you would post a copy of your workbook with your question I might be able to advise you on how to split data from presentation but not knowing your work flow my advice is likely to be less than perfect. Therefore I suggest that you design the database first, then data entry and data modification, and finally data presentation. Come here with questions how to get data into and out of your DB.

Sorry to give you this suggestion. You must feel that it isn't what you want. That feeling will disappear once you start enjoying the benefits of a better workbook design. Not only is everything easier to do but many things become possible which were quite unthinkable before. Moreover, it isn't very hard to do because most of the underlying analysis has already been done. It must only be put together differently.


Answer the Question

You must create an account to use the forum. Create an Account or Login