Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking up dates
Hi, i have a table as shown in the attached picture. Its a registeratio database built in excel. Is it possible to have a report on a seperate spreadsheet which whe the user puts in a name it'll match to the name on this spreadsheet an return the dates for when an employee is off sick or on holiday or lat and so on. So say i wanted to know mr x's sick days, it would have to find hi sick boxes, (which are red with an S) under his name and return th values in the first column which is the dates. Is this possible? So far i have this code which will look up the amount of times someon is off sick or whichever: Code ------------------- Sub Macro_ChangeData() Dim Stringd As String Stringd = InputBox("Please enter an Employee's name as it appears exactly in the register") Range("C9").Formula = "=VLOOKUP(""" & Stringd & """,Hidden!$A$2:$K$134,1,FALSE)" Range("C11").Formula = "=VLOOKUP(""" & Stringd & """,Hidden!$A$2:$K$134,2,FALSE)" Range("C13").Formula = "=VLOOKUP(""" & Stringd & """,Hidden!$A$2:$K$134,3,FALSE)" Range("C15").Formula = "=VLOOKUP(""" & Stringd & """,Hidden!$A$2:$K$134,4,FALSE)" Range("C17").Formula = "=VLOOKUP(""" & Stringd & """,Hidden!$A$2:$K$134,5,FALSE)" Range("C19").Formula = "=VLOOKUP(""" & Stringd & """,Hidden!$A$2:$K$134,6,FALSE)" Range("C21").Formula = "=VLOOKUP(""" & Stringd & """,Hidden!$A$2:$K$134,7,FALSE)" Range("C23").Formula = "=VLOOKUP(""" & Stringd & """,Hidden!$A$2:$K$134,8,FALSE)" Range("C25").Formula = "=VLOOKUP(""" & Stringd & """,Hidden!$A$2:$K$134,9,FALSE)" Range("C27").Formula = "=VLOOKUP(""" & Stringd & """,Hidden!$A$2:$K$134,10,FALSE)" Range("C29").Formula = "=VLOOKUP(""" & Stringd & """,Hidden!$A$2:$K$134,11,FALSE)" End Sub ------------------- The 'Hidden' window is shown in a screenshot, this just takes it values it needs by refferencing the different cells on othe spreadsheets and is a spreadsheet where all the data is collated s that this report im trying to do will work. So so far i can see how many times an employee has been off sick, but need to know if its possible to show the dates of those times. If anyone can help that would be greatly appreciated. Many thank +------------------------------------------------------------------- |Filename: hidden.jpg |Download: http://www.excelforum.com/attachment.php?postid=4121 +------------------------------------------------------------------- -- alymcmorlan ----------------------------------------------------------------------- alymcmorland's Profile: http://www.excelforum.com/member.php...fo&userid=2765 View this thread: http://www.excelforum.com/showthread.php?threadid=49331 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking computer dates (time) to spreadsheet dates that have formu | Excel Worksheet Functions | |||
Toggle a range of Julian dates to Gregorian Dates and Back | Excel Programming | |||
Identifying unique dates in a range of cells containing dates... | Excel Discussion (Misc queries) | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) |