Selecting names based on Teams (using dropdown)

[max]

Hi,

The excel sheet is at
[http://www.4shared.com/file/136709270/209bb11/ITOpsShiftData.html
]
In the sheet attached i would want to use macro features to the minimal but
if needed then i can go ahead with using macros.

This is like a master list to me wherein in one shot i should be able to see
data regarding the shift details of employees. There are about 14 employees,
but can extend to 60
These employees could fall into different departmetns
As mentioned in Row8(D8,E8..)
I send this out as a common template to the team leaders of these teams.

My question is at D2 i want a dropdown containing group names
(DBA,NSS,BSS,SMC..).
So when a team lead receives this sheet and when he selects from the
dropdown for ex DBA only those columns of employees who belong to DBA should
appear.
Once they fill and send it back, for me it should be easy to simply copy them.

In the dropdown i also want an option called "All" so that when all is
clicked all the resources appear but should be sorted based on teams like
first DBA resource,BSS resource etc..

At first i want to achieve this so that i can proceed futher.


Note: I do know that if i did a transpose of days and resources i could
filter on teams, but i need to update other system seeing this data and so
the column view for each resource would be easier for me

Many thanks
max

 

[p45cal]

This is a macro solution I'm afraid.
I had to move the dropdown cell to B2 from D2 because it got hidde
sometimes! (So you ought to remove the validation from D2.)
Paste the following code where you end up if you right-click the shee
tab and choose View Code.. (ie. the sheet's code module):
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 8 Then
'This section keeps the Data Validation up to date if the depts. i
row 8 are added to or changed:
Set DeptsRng = Intersect(Range("D8").CurrentRegion, Rows(8)
Range("DP"))
xxx = DeptsRng
xxx = Application.Index(xxx, 0)
For i = 1 To UBound(xxx)
For j = i + 1 To UBound(xxx)
If xxx(i) = xxx(j) Then xxx(j) = Empty
Next j
Next i
For i = 1 To UBound(xxx)
If xxx(i) <> Empty Then DropDownStr = DropDownStr & xxx(i) & ","
Next i
DropDownStr = DropDownStr & "All"

With Range("B2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:
_
xlBetween, Formula1:=DropDownStr
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
'this bit hides/shows columns:
If Target.Address = "$B$2" Then
Set DeptsRng = Intersect(Range("D8").CurrentRegion, Rows(8)
Range("DP"))
DeptsRng.EntireColumn.Hidden = True
If Range("B2") = "All" Then
DeptsRng.EntireColumn.Hidden = False
Else
For Each cll In DeptsRng.Cells
If cll.Value = Range("B2").Value Then cll.EntireColumn.Hidden
False
Next cll
End If
End If
End Su

 

[max]

Dear P45cal,
Amazing!!!!!!!!!!!!!!!!! I have no words to explain your expertise, thanks
a lot.
Well that part is working fine now.
I just have to achieve one more thing. From D10toR39 as in this sheet. I
have actually the shift data. Well i have 2 questions here.
1> When user chooses from Dropdown "vacation,Off Day,Holiday the cell
colr shold be white. When General i want one color, when ITOps-2ndshift i
want cell to change color when selected...

2> i tried placing a text box on the top near D1 to G1 to explain the time
Like General: 08:00 - 17:00 "Color"
but due to freeze panes that will not be visile to user i want it to
stay there regardless of we move columns. How can i do this or any other way
to show that details in the same sheet. Please suggest
Thanks again for your time
Max

 

[p45cal]

Dear P45cal,
Amazing!!!!!!!!!!!!!!!!! I have no words to explain your expertise
thanks
a lot.
Well that part is working fine now.
I just have to achieve one more thing. From D10toR39 as in this sheet
I
have actually the shift data. Well i have 2 questions here.
1> When user chooses from Dropdown "vacation,Off Day,Holiday the cell
colr shold be white. When General i want one color, when ITOps-2ndshif
i
want cell to change color when selected...

For this you need to use the 'formula is' aspect of Conditiona
formatting for the cells; say you're in F10, you could put formulae i
each of the three conditions (xl2003) such as:

=OR(F10="Off Day",F10="Vacation",F10="Holiday")
=F10="ITOps-2ndShift"
=F10="General"

and put whatever colours/formatting you want to use in each case. The
you can copy that conditional formatting to other cells.

2> i tried placing a text box on the top near D1 to G1 to explain th
time
Like General: 08:00 - 17:00 "Color"
but due to freeze panes that will not be visile to user i want it to
stay there regardless of we move columns. How can i do this or an
other way
to show that details in the same sheet. Please suggest
Thanks again for your time
Max

In the Properties tab of the Format Text Box dialogue box, choos
'Don't move or size with cells'. The box will remain in place, visible
regardless of which columns are hidden.


I've streamlined the code I posted before, especially the bit tha
creates a list for the validation dropdown (it did too much looping fo
my liking) as well as removing some now redundant lines and moving on
or two others.:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 8 Then
'This section keeps the Data Validation up to date if the depts. i
row 8 are added to or changed:
Set DeptsRng = Intersect(Range("D8").CurrentRegion, Rows(8)
Range("DP"))
Set uniquelist = CreateObject("Scripting.Dictionary")
For Each k In DeptsRng.Value
If Not uniquelist.exists(k) Then
uniquelist.Add k, k
DropDownStr = DropDownStr & k & ","
End If
Next k
DropDownStr = DropDownStr & "All"
With Range("B2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:
_
xlBetween, Formula1:=DropDownStr
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End If
'this bit hides/shows columns:
If Target.Address = "$B$2" Then
Set DeptsRng = Intersect(Range("D8").CurrentRegion, Rows(8)
Range("DP"))
If Range("B2") = "All" Then
DeptsRng.EntireColumn.Hidden = False
Else
DeptsRng.EntireColumn.Hidden = True
For Each cll In DeptsRng.Cells
If cll.Value = Range("B2").Value Then cll.EntireColumn.Hidden
False
Next cll
End If
End If
End Su

 

[max]

Thanks for your inputs have done them.
One last thing i got to ask
When i select "All" from the dropdown, the columns appear in teh form
entered , randomnly.
Is there a way that when "all is selected" i get the Teams ordered(sorted)
like all CTS,then all DBA, ...
Thanks again
Max

 

[p45cal]

Thanks for your inputs have done them.
One last thing i got to ask
When i select "All" from the dropdown, the columns appear in teh form
entered , randomnly.
Is there a way that when "all is selected" i get the Team
ordered(sorted)
like all CTS,then all DBA, ...
Thanks again
Max

The macro only hides/shows columns, it doesn't move them. So they're i
the same order that you entered them in.
Sort them, once , manually, using the horizontal sort option when yo
select 'Options' in the sort dialogue box. It'll have the added bonus o
the dropdown having departments in order too

 

[max]

Thanks a ton for all that you did to me on this topic
thanks
max

 

[max]

Dear p45cal,
I am back with the same sheet.I have achieved all with your help but now i
had applied conditional formattingfor the cells to choose shifts. but for
2003 users they would not be able to see all coloring. How can i go about
this now, is there a way to fix this.
Also in the excel can i make the cell B2 to keep blinking/flashing to draw
users attention
my file is at link
http://www.4shared.com/file/137908114/75b96b76/Working.html

Thanks
max

Thanks a ton for all that you did to me on this topic
thanks
max

 

[p45cal]

There have been significant changes to Conditional Formatting in xl2007
I can only suggest redeveloping that part in xl2003 as most thing
developed therein will work in xl2007 - I see you used a lot of rule
per cell.

With respect to the blinking/flashing B2, I would strongly recommen
that you DON'T have any flashing cells; it is very annoying to an en
user. Once he knows where that cell is, he no longer needs to b
perpetually reminded of it (A flashing cell is useful if somethin
changes on the sheet, especially as a result of external data coming in
then there might be justification for a flashing cell, especially, i
there might be more than one cell that you need to draw the user's eye
to).

Another reason for not using a flashing cell (I don't -think -MS hav
introduced a flashing format even in xl2007) is that it's complicate
and a bit of a resource hog, with a little macro running for every flas
of the cell.

Do a google for 'blinking excel cell' and you'll see some sample cod
and discussions on how it's not very desirable.

I'd just highlight the cell with colour or a comment which pops up o
mouse over