Work tools - More Simple the Better

WorkUse

New Member
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>

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
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
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.

Replies
3
Views
91
Replies
0
Views
100
Replies
7
Views
165
Replies
18
Views
246
Replies
0
Views
80

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.

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

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