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>
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

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,132,895
Messages
5,655,830
Members
418,242
Latest member
k3itall

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top