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

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:

how would i go about doing that?


--
alymcmorland
------------------------------------------------------------------------
alymcmorland's Profile: http://www.excelforum.com/member.php...o&userid=27652
View this thread: http://www.excelforum.com/showthread...hreadid=493316

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?