Looking up dates

[alymcmorland]

Hi,

i have a table as shown in the attached picture. Its a registeration database built in excel.

Is it possible to have a report on a seperate spreadsheet which when the user puts in a name it'll match to the name on this spreadsheet and return the dates for when an employee is off sick or on holiday or late and so on.

So say i wanted to know mr x's sick days, it would have to find his sick boxes, (which are red with an S) under his name and return the 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 someone is off sick or whichever:

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 its values it needs by refferencing the different cells on other spreadsheets and is a spreadsheet where all the data is collated so 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 i need to know if its possible to show the dates of those times.

If anyone can help that would be greatly appreciated.

Many thanks

[keepITcool]

why use code at all?

change your formula to accept input from a cell
then put a datavalidation with lookup on that cell
to show the employee names.

since the employeenames are not on the same sheet
you must define a (global) name for the employee list
before creating the datavalidation.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


alymcmorland wrote :

>
> Hi,
>
> i have a table as shown in the attached picture. Its a registeration
> database built in excel.
>
> Is it possible to have a report on a seperate spreadsheet which when
> the user puts in a name it'll match to the name on this spreadsheet
> and return the dates for when an employee is off sick or on holiday
> or late and so on.
>
> So say i wanted to know mr x's sick days, it would have to find his
> sick boxes, (which are red with an S) under his name and return the
> 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 someone
> is off sick or whichever:
>
>

[alymcmorland]

how would i go about doing that?

[keepITcool]

1. Insert Names/ Define
e.g.
EmpTable for the range Hidden!a2:k134
EmpNames for the range Hidden!a2:a134

(you can make this "dynamic range names"
google on that term should give you some examples.


2. in a free cell on your "input sheet"
e.g. C5

Data/Validation
Allow LIST
Check INCellDropdown
Source =EmpNames (name as defined above, be sure to include the =)

3. Enter your VLookup Formulas in C9: C29
=Vlookup($c$5,EmpTable,1,0)

Done.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


alymcmorland wrote :

>
> how would i go about doing that?