Reset button after anniversary date (vacation schedule)

[jerger]

11/15 solved!

sharing some functions... also used a case statement to do this with a button.

ActiveSheet.Unprotect
' temp1 = Range("p6").Value
' Range("p6").Value = ""

Range("p5").Value = Range("p6").Value
' Range("p5").Value = temp1
Range("B14:AF37").Select
Selection.ClearContents
Range("u7").Value = 40

Range("U8").Select
Selection.ClearContents
'------------------------------

Select Case Range("P7").Value
Case 0
'Range("p5").Value = Range("p6").Value

Range("P4").Value = 40
Case 1

Range("P4").Value = 80
Case 2

Range("P4").Value = 80
Case 3
Range("P4").Value = 120
Case 4
Range("P4").Value = 120
Case 5
Range("P4").Value = 120
Case 6
Range("P4").Value = 120
Case 7
Range("P4").Value = 120
Case 8
Range("P4").Value = 120
Case 9
Range("P4").Value = 120
Case 10
Range("P4").Value = 160
Case Else
Range("P4").Value = 160
End Select




** update 11/14**
added my basic menu (click the blue shape)
-its just a place holder. the values come from the database, not the vb code.
stuck on how to use them to set the actual cells in the subsheets

** update*
I really just need to learn how to use the "define name" fields as row/column lookups then paste the hours into that cell.

example: select name (which is the column), select date (which is the row)...
then have a function to place the "hours selected" into the cell... that is for this name and date selection.

i'm confused on how to do that... the user selects type of vacation(which is the sheet name).. so the formula basically:
needs to find the sheet, look up the date/name... at this cell insert the hours.

Just a start or hint would be nice... my formulas havn't worked yet.

IF THERE IS AN ALTERNATE METHOD PLEASE SHARE

goals:
1. after anniversary date passes, row for user turns red or any color
2. a reset button so that once you know their anniversary date has passed it resets the static time off hours, sets the new "carry over hours" and adjusts the vacation hours for the new year (may also need to reset the sub tables which i can figure out later)

I did post in the functions forum but my goals have changed and I honestly do not think this is possible with a simple function.

Currently I am using a lookup table.
Old goal: reset carry over hours after 3 months which they would expire (deemed unnecessary, i can skip this step)

for goal #1:
I think I could compare the date/month with anniversary date's date month correct? (but i think it would need to be reset the day after their anniversary date to prevent a reloop/constant reset of values)

for goal #2: reset button: most likely needs to be in a specific order

a. carry over hours = previous year's vacation hours remaining
b. vacation hours per year gets re-adjusted:
=VLOOKUP(DATEDIF(A8,TODAY(),"y"),$S$8:$T$10,2)
c. personal hours per year is static: 40
(therefore the secondary field which is a function will self adjust for remaining personal hours)
d. sick hours per year = 40 hours, will need to be reset similiar to "step c"
e. Fl hol = 8 (floating holiday gets reset)
f. other = not sure, this might still be manually adjusted and can be ignored for now

other notes:
1year=40 hours
after 1 year anniversary =80 hours
3 years-9 =120 hours
10 years+ = 160hours
Vacation only rolls over for 3 months after anniversary date (this is a concern after part a is completed)

[jerger]

*** other ideas ***
-use the drop down menu to set the name, then use vlookup to find the matching name, the column, is then stored. into setC
-use the drop down menu to set the date, then use hlookup to find the matching date, the row , is then stored. into setR

then place the value in the SetC,SetR
-which i'm not sure how to do

researched similiar formulas: (may use a modification to find the name in the column on the designated sheet set by the type of vacation hours selected)
= Not( Isna( Match(c2, A100:a200, 0 ) ) )

or:

= COUNTIF(A100:A200,"="&C2)>0


i like these ideas: i would need to search the column for the name... (column A) and row 1 :

ex:
compaing Col. B with Col A. now write this formula in C1 and drag it down
=IF(ISERROR(VLOOKUP(B1,$A$1:$A$8,1,FALSE)),"New Data","")
or:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
"
Use the formula (in cell D1) =VLOOKUP(A1,$B$1:$C$9,2,FALSE)
http://en.kioskea.net/forum/affich-1...-value#p156956
Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.

The values in the first column of table_array can be text, numbers, or logical values.

Uppercase and lowercase text are equivalent. "

[jerger]

any ideas?

[jerger]

i am going to be creating a webform i think as well to easily add new entries.
http://forums.devx.com/archive/index.php/t-78956.html
what i want to do is:

step 1:
user selects a drop down menu (grabs name), grabs date, month and year...
then selects the type of time off (lets say personal hours), then enters lets say 8.

with current db... there is teh same names in the same order on each subsheet. subsheet example = personaltime

column A=names
one of the rows=dates

what i would like to do is in step 1 above is use this to set the number in the column and row to the number of hours off.

so find name... find date (then put data in row (name) and column (date)... for that single user

does that make sense?

[jerger]

this helps change cell color based on condition:
= ROUND(NOW()-$A8,0)>365

need to modify it so it doesnt change back unless flag =0

[jerger]

hardest part:

finding the sheet... using the date and names as the column/row then placing the hours in that cell.

any ideas?

finding related links
http://www.ozgrid.com/VBA/select-case.htm
http://www.contextures.com/xlNames02.html
http://www.excelforum.com/excel-new-...ple-cells.html
http://www.excelforum.com/excel-work...ing-items.html
http://www.excelforum.com/excel-gene...orksheets.html

http://www.excelforum.com/excel-gene...orksheets.html

http://www.excelforum.com/excel-gene...vs-column.html

[jerger]

anyone?

i really am worried on how to:
find user, find date... place hours...

my functions are getting slopppy and i havn't had any progress. this is a multisheet db

[jerger]

i think i need to be more 2-dimensional
http://msdn.microsoft.com/en-us/libr...ffice.10).aspx
MATCH(lookup_value,lookup_array)

ex
=INDEX(Tables!A26:C65,MATCH(A31,Tables!A26:A65,0),MATCH($B$16,Tables!A26:C26,0))

other ideas:
http://www.excelforum.com/excel-prog...i-need-it.html
http://www.excelforum.com/excel-prog...ng-macros.html
http://www.excelforum.com/excel-prog...ng-macros.html
http://support.microsoft.com/kb/830287
http://www.contextures.com/xlautofilter01.html

[jerger]

any ideas or alternate methods?

i think i need to do vlookup (name) then hlookup (date)
-then find a way to fill that referanced cell with the hours selected

[jerger]

bump for help with using variables to find name, date and then paste the value selected from the drop down menu

http://office.microsoft.com/en-us/ex...216891033.aspx


this is close to what i need to do:
http://sites.google.com/site/normay/excel-files

'
' Reset_For_New_Year()
' Macro recorded 1/1/2003 by N May
'
Sub Reset_For_New_Year()

Msg = "Do you want to reset your Attendance History workbook for a new year?" & Chr(13) & _
"All information associated with the previous year will be deleted." & Chr(13) & _
"All employee information will remain."
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "Reset for a new year"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then

Application.ScreenUpdating = False
k = Worksheets.Count
For i = 1 To k
If Worksheets(k - i + 1).Visible Then
Worksheets(k - i + 1).Select
If Range("AJ26").HasFormula = True Then
ActiveSheet.Unprotect
Range("B12:AF35").Select
Selection.ClearContents
Range("U5").Select
Selection.ClearContents
'------------------------------------------------------
'This section will delete all text in the text box
ActiveSheet.Shapes("Text Box 4").Select
Selection.Formula = "$R$2" 'This will assign the cell value to the text box
ActiveSheet.Shapes("Text Box 4").Select
Selection.Formula = "" 'This will delete the cell value to the text box
Selection.Characters.Text = "" 'This will delete up to 255 characters from the text box
'------------------------------------------------------
Range("P5").Select
Selection.ClearContents
Range("A2").Select
ActiveWindow.SmallScroll Down:=-300
ActiveWindow.SmallScroll ToRight:=-200
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Else
'Range("B2").Select
End If
End If
Next i
Sheets("Set Up").Select
Range("B6:B22").Select
Selection.ClearContents
Range("B2").Select
Selection.ClearContents
Application.ScreenUpdating = True
Else
Sheets("Set Up").Select
Range("B2").Select
End If
Application.ScreenUpdating = True
End Sub