Filtering columns

**maximpinto** · 05-13-2009, 05:40 AM

I 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.

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

**mubashir aziz** · 05-13-2009, 05:56 AM

Can you post sample Sheet ???

**maximpinto** · 05-13-2009, 11:54 PM

Dear
Please find attached sheet

Thanks
max

**mubashir aziz** · 05-14-2009, 02:58 AM

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

Although you have Systems in A1 but how excel will came to know that which resources are working for "EAI" as i didn't see these systems in any other place in your sheet. use some fields to mention that sources are working for this system system.

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.

**maximpinto** · 05-14-2009, 03:34 AM

Hi ,
I do agree with your statement, as of now just to visualise i was using some coloring mechanism.
But i was thinking that if we have these resources tagged to a "system" somewhere in other sheets and if a macro is written (which i canot do), that was why i was asking how can i achieve all this.

HAving them defined somewhere ..
that way i can also update resources if they leave organization or if anyone joins organization.

Or please give me ideas if i can do this someother way.

**Roger Govier** · 05-15-2009, 06:18 AM

Hi

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)

Please Login or Register  to view this content.

**maximpinto** · 05-15-2009, 06:40 AM

Dear,
I am not sure if you have understood what i am trying to achieve. I am not bothered about x or o in the values

Alll i need is for ex in the sheet that you sent back to me

if i choose RAFMS from a1 dropdown i shld get the resultant sheet like the one i have attached
similarly with other systems.

And if i select ALL all them should be shown as in the sheet you attached.
Pleas help thanks for you time

Regards+Prayers
max

**Roger Govier** · 05-15-2009, 12:59 PM

Hi Max

I apologise.
I had not picked up the fact that cell A1 had Data Validation with a dropdown list.
I was assuming that you were using the drop down on cell B2 to make your selection - by choosing Project ID.

Waht determines which rows belong to RAFMS, CC&C etc?

The easiest way to get it to work, would be to Indert a new column at A, and allocate the names to each row.
Make the selection on new cell cell A2, then it would work as already shown.
I have added a new copy of the workbook, where I have done this and arbitrarily allocated Projects to different lines.

If this is not suitable, then come back to me with informtaion on what determines what belongs to each Project.

**maximpinto** · 05-16-2009, 11:22 AM

Dear Roger,

Thanks a lot for your response. I am almost there with your valuable suggestion and would convince our seniors but if the following could also be achieved i would be really happy to have the sheet fit

When i choose for ex "MEdiation billing" i get only "Shankit" as resource,i agree to this fact because of the "x" present undr his name,only he is shown as utilized

Now when i click button "show All" i will get all resources of other systems too. Instead when i click "Show All" button i should see only the resources of that system not utilized like in this case "like "Shambu,Shoma,max,sube" just for the manager to know they wer not used for that week.Can you please make changes to reflect this.

And next when this is achieved when i click button "Hide marked" it will again show me only resource utilized like in this case "Shankit" which is correct.(i changed button name to "Hide Resources not utilized").This functionality is correct as expected.

This is optional request. IF it can be achieved i will be greatful else no problem.

Can i hav another 3rd button called "Company utilization", wherein when i click this button all the filtering will be taken away and i get the whole sheet with nothign hidden (i.e., i get all the rows(i mean all projects for all systems) and columns with all resources both utilized and not utilized)

similarly after i get this data if i go back and click "hide marked"button it should hide non-utilized resources.

Thanks a lot for all your help.
Thanks
max

PS: i hope this sheet is compatible with both 2003 and 2007.I am using 2007 when i sent u thsi sheet.

**Roger Govier** · 05-17-2009, 02:25 PM

Hi Max

Only just seen your latest posting.
Can't do anything right now, but I will take a look tomorrow morning.

**maximpinto** · 05-18-2009, 03:05 AM

Alright Roger, Will be thankful if you can do that for me by tomorrow.
thanks
Max

**maximpinto** · 05-20-2009, 11:06 PM

Dear Roger,
Can you please help me in finishing this. Since i hv produced it got to work it right
Thanks
max

**maximpinto** · 05-24-2009, 11:13 PM

Dear Roger,
I did not get any response over this
Thanks
max

Filtering columns

LinkBack

Thread Tools

Rate This Thread

Display

Filtering columns

Re: Filtering columns

Re: Filtering columns

Re: Filtering columns

Re: Filtering columns

Re: Filtering columns

Re: Filtering columns

Re: Filtering columns

Re: Filtering columns

Re: Filtering columns

Re: Filtering columns

Re: Filtering columns

Re: Filtering columns

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions