Work tools - More Simple the Better

WorkUse

New Member
Joined
Mar 4, 2014
Messages
2
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?
EmployeePostion
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>
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
the source data in sheet 1 is like this

Excel Workbook
ABCDE
1EmployeePostion3/2 Sunday3/3 Monday3/4 Tuesday
2John DoeTeam 17.5off7.5
3Janet DoeTeam 2off7.57.5
Sheet1



try this macro and SEE SHEET2

Code:
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

New Member
Joined
Mar 4, 2014
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,516
Messages
5,469,097
Members
406,635
Latest member
jfhunter64

This Week's Hot Topics

Top