Filtering Columns

[Max]

have an excel with the following information



A1 - Is a dropdown which will have names of the systems (EAI,Mediation,CDF
...,All)

Row2 will be header which will have following data


B2 - Project Id
C2 - System ID
D2 - Project name
E2 - Project Phase
F2 - Status
from g2 to GN2 there are about 60 resources names entered

From C3 to F3 the data wil be entered and appropriately a chk mark will be
made to the corresponding resource wroking on that project

Say i have about 10 systems provided as dropdown for entering data in A1
(Ex: EAI, MEdiation, .....)
There are resources working for each system

My question is now when i apply filtering on a system name i shoud be able
to show only those resources working for that system and all others should be
hidden

Also under the "system" dropdown i will have an option "All", when "All"
option is selected i should be able to see all the data for all the systems.

EX: if i take system "EAI" and if the resources working for eai are there in
AO2 to AX2 then i want the resources from AX2 onwards and before AO2
not to be shown.
If i take a system "Mediation" and if its resources are from Z2 to AI2
then i want resources before Z2 and after AI2 notto be shown.
If "All" option is chosen then i should be able to see the whole sheet
with all the data for all the systems and all resources.

Can anyone throw light on this with a macro. Hope i am clear.
Also i know that there is no link between system and resources how can i do
this too.


IF this can also be achieved in a simpler way please do let me know

 

[Ronald R. Dodge, Jr.]

I think you got some of your wording mixed up, but let's see what you mean:

I will make the following assumptions

Each row serves as a record like a database program

Each column serves as a field like a database program

Columns A through F will always be shown

Columns G through IV are either empty or contain information to a particular
system.

If you select the option of "All" in column "A", then you want all columns
visible, otherwise only columns A through F and the columns pertaining to
that particular system.


Issues/limitation of Excel

When hiding cells, can only hide an entire column, entire row, or entire
worksheet, not any individual cell by itself.

How to get around this issue:

Have both the font color and the interior color (aka background color) set
to the same color. Doesn't really hide it, but for as long as such cells
are not select with them being an inactive cell, then the data within such
cells won't be seen within those cells themselves.

You can create User Defined Functions within VBA, but they can only impact
certain aspects.


What can you do?

First, look for what fields are in common of the different systems.

Next, setup a table in another worksheet to show the various system
information (Note, under this method, the table will serve as your *All*
option as there won't be that option on the main worksheet with this method)

Keep columns A through F as is.

From columns G on out to the last column needed, you can setup formulas to
pull the necessary data from the table in the other worksheet. Note you can
use either VLOOKUP or the combination of the following functions IF,
ISERROR, MATCH, INDIRECT, ADDRESS, and possible even COLUMN. Using the
VLOOKUP is using the assumption that what is in A will always be listed in
the table as well. However, if that won't be the case, then you can use the
other set of functions for error trapping purposes. Regardless which method
you use, you can use the COLUMN function to make the formula that much more
dynamic rather than static in the event that columns are added/removed from
the worksheet the table is on. When columns are added/removed from the
worksheet with the table on it, the formulas that has the COLUMN function
will also automatically adjusted while those formulas with the status column
number in the column argument won't adjust.

One such example is the following formula in cell Sheet1!G2 with the table
on Sheet2:

=IF(ISERROR(MATCH(A2,Sheet2!A:A,0)),"",INDIRECT(ADDRESS(MATCH(A2,Sheet2!$A:$A,0),COLUMN(Sheet2!B:B),,,"Sheet2")))

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Max]

Dear Ronald,
First of all i would like to thank you for the time you have spent in
understanding my problem. Since i am very new i cannot understand your
solution pretty much clearly but it helped me a lot in understanding the
limitations.
IF you dont mind can you go to this like
"http://www.excelforum.com/excel-programming/683432-filtering-columns.html"
I have attached a sample excel sheet there. Since i have no option for
attachment here i could not

I do agree with the solution whichyou have mentioned, but can you get it
done for me,
I would appreciate your efforts and time

Thanks
Max

 

[Roger Govier]

Hi Max

See the response, with a working file, that I have posted for you in MrExcel
forum.

This utilises some very clever, and very succinct, code from Debra Dalgleish
for hiding columns.
http://www.contextures.com/HideMarkedCols.zip

 

[Roger Govier]

Hi Max

For the sake of completeness, and for those not able to visit the other
site, here is the complete response (apart from the uploaded file) that I
posted there.

You can achieve what you want utilizing a technique from Debra Dalgleish's
site for hiding columns.
http://www.contextures.com/HideMarkedCols.zip

I have copied her code into a module in your book, and added buttons.

In row 1, columns H to BO I have added the following formula
=IF(SUBTOTAL(3,H3:H1000)=0,"X",0)
I have made the font colour the same as the background, so the values don't
show.
This makes use of the Subtotal function, to check whether any of the columns
that are visible after you filter for your project type contain an entry. If
they don't, then an "X" is place in the cell, otherwise it is a 0.

Debra's code then hides columns where there is a formula that gives rise to
a text entry in row 1. (Note I have modified her code, so that columns are
not hidden when All projects are selected i.e. when Filtermode is false)

Sub HideCol()
' hides the columns marked with an X
ShowCol
If ActiveSheet.FilterMode Then
Rows("1:1").SpecialCells(xlCellTypeFormulas, 22) _
..EntireColumn.Hidden = True
End If
End Sub

Sub ShowCol()
Cells.EntireColumn.Hidden = False
End Sub

Whilst you can use the buttons on the form to Hide or show columns, I have
also added some event code to the sheet, so that once you change the
selection with the dropdown on cell A2, to change the Project, it calls the
Hidecols macro.

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Application.ScreenUpdating = False
Call HideCol
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[Max]

Sir i have posted back reply please view it
thanks
max