Hi,

The excel sheet is at
[http://www.4shared.com/file/13670927...ShiftData.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

This is a macro solution I'm afraid.
I had to move the dropdown cell to B2 from D2 because it got hidden
sometimes! (So you ought to remove the validation from D2.)
Paste the following code where you end up if you right-click the sheet
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. in
row 8 are added to or changed:
Set DeptsRng = Intersect(Range("D8").CurrentRegion, Rows(8),
Range("D: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("D: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 Sub


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=140062

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" wrote:


This is a macro solution I'm afraid.
I had to move the dropdown cell to B2 from D2 because it got hidden
sometimes! (So you ought to remove the validation from D2.)
Paste the following code where you end up if you right-click the sheet
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. in
row 8 are added to or changed:
Set DeptsRng = Intersect(Range("D8").CurrentRegion, Rows(8),
Range("D: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("D: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 Sub


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=140062

max;511100 Wrote:
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...

For this you need to use the 'formula is' aspect of Conditional
formatting for the cells; say you're in F10, you could put formulae in
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. Then
you can copy that conditional formatting to other cells.

max;511100 Wrote:
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" wrote:


This is a macro solution I'm afraid.

In the Properties tab of the Format Text Box dialogue box, choose
'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 that
creates a list for the validation dropdown (it did too much looping for
my liking) as well as removing some now redundant lines and moving one
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. in
row 8 are added to or changed:
Set DeptsRng = Intersect(Range("D8").CurrentRegion, Rows(8),
Range("D: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("D: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 Sub


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=140062

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" wrote:


max;511100 Wrote:
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...

For this you need to use the 'formula is' aspect of Conditional
formatting for the cells; say you're in F10, you could put formulae in
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. Then
you can copy that conditional formatting to other cells.

max;511100 Wrote:
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" wrote:


This is a macro solution I'm afraid.

In the Properties tab of the Format Text Box dialogue box, choose
'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 that
creates a list for the validation dropdown (it did too much looping for
my liking) as well as removing some now redundant lines and moving one
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. in
row 8 are added to or changed:
Set DeptsRng = Intersect(Range("D8").CurrentRegion, Rows(8),
Range("D: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("D: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 Sub


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=140062

max;511917 Wrote:
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

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


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=140062

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

"p45cal" wrote:


max;511917 Wrote:
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

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


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=140062

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/13790811...6/Working.html

Thanks
max

"max" wrote:

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

"p45cal" wrote:


max;511917 Wrote:
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

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


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=140062

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

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

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

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

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


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=140062