Work tools - More Simple the Better

[WorkUse]

Hi everyone!

I am an Excel novice looking to use it for a a few simple (I think...) tricks.

#1 - There is an excel spreadsheet listing all employee names. I need something to pop up when the sheet is opened to remind us which employees are ready to choose benefits. Benefits are always due on the 1st of the month. Any ideas?

#2 - We have a spreadsheet for the schedule that has the following columns: Name, Position (same for regular employees, other employees' position is simply 'Relief') and then it proceeds to list days (M-T-W-H-F-S-S-M-T-W-H-F-S....etc, etc) See example. The idea is to have another sheet pull ONLY the employees that are working that day, and fill in what position they are assigned to be used for a sign-in sheet and also as a way to see who is on daily. Any thoughts?

Employee	Postion	3/2 Sunday	3/3 Monday	3/4 Tuesday
John Doe	Team 1	7.5	off	7.5
Janet Doe	Team 2	off	7.5	7.5

<tbody>
</tbody>

 

[venkat1926]

the source data in sheet 1 is like this

Excel Workbook
	A	B	C	D	E
1	Employee	Postion	3/2 Sunday	3/3 Monday	3/4 Tuesday
2	John Doe	Team 1	7.5	off	7.5
3	Janet Doe	Team 2	off	7.5	7.5
Sheet1

try this macro and SEE SHEET2

Sub test()
Dim ddate As String, emp() As String, position() As String, cfind As Range
Dim n As Integer, k As Integer, dest As Range
 Application.ScreenUpdating = False
Worksheets("sheet2").Cells.Clear
 
 ddate = InputBox("type date only e.g 3/4 no doubole quotes")
With Worksheets("sheet1")


Set cfind = .Rows("1:1").Cells.Find(what:=ddate, lookat:=xlPart)
If Not cfind Is Nothing Then
n = WorksheetFunction.CountA(.Columns(cfind.Column)) - 1
ReDim emp(1 To n)
ReDim position(1 To n)
End If
For k = 1 To n
'If cfind.Offset(k, 0) = "off" Then GoTo nextk
emp(k) = .Cells(cfind.Offset(k, 0).Row, 1)
position(k) = .Cells(cfind.Offset(k, 0).Row, 2)
nextk:
Next k
With Worksheets("sheet2")


Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
dest = ddate
For k = 1 To n
If cfind.Offset(k, 0) = "off" Then GoTo nnextk
dest.Offset(k - 1, 1) = emp(k)
dest.Offset(k - 1, 2) = position(k)
nnextk:
Next k
End With
End With
MsgBox "macro done"
Application.ScreenUpdating = True


End Sub

 

[WorkUse]

Wow! That is exactly what I needed!

I have been trying to make it work for the actual document and have been having some issues. I sent you an email, subject="book1.xlsx" asking for further direction.

Thanks again and I look forward to seeing your response.