How many worksheets are we talking about?


=IF(A1='John'!A1,'John'!B1,IF(A1='Pete'!A1,

and so on

if many you can download Laurent Longre's Morefunc and use a function called
THREED
which can make a 3 - D range into a 2 - D range


=INDEX(THREED('John:Sue'!$B$1),MATCH(A1,THREED('Jo hn:Sue'!$A$1),0))


http://xcell05.free.fr/


English descriptions at

http://www.rhdatasolutions.com/morefunc/

--

Regards,

Peo Sjoblom




"Mugen123" wrote in message
...
Hello, I've been struggling with this issue for a while, and am hoping
someone could help me.

I have made a workbook for recording employee hours worked, and have
assigned each employee a tab, each worksheet is used to input hours worked
and provides a summary on each worksheet as to total hours worked,
overtime,
etc for the month.

I also have a worksheet that contains a complete listing of all staff and
indentifies each with an Employee# (each employee number is unique). I
have
used the VLOOKUP funtion on individual employees' worksheets so that all
data
related to the employee (ie. name, position, etc.) will fill out the sheet
automatically by using their employee ID# from the listing tab.

Now comes my dilemma: I would like to make a 'Summary' sheet that shows
the
hours worked by each employee by comparing their Employee number listed on
the Summary sheet to the Employee Number listed on the employee's hourly
tracking sheet. I want to do this so that if another employee is added
later, i don't have to reference the sheet individually over and over
again.

For example:
I have 3 employees with hourly tracking sheets named 'John', 'Pete',
'Sue'.
They each have their Employee# listed on their sheets in cell A1.

On the summary sheet I have their employee numbers listed, and would like
a
formula that compares that number to the numbers listed in cell A1 across
worksheets John:Sue!. And then display a value from a cell from that
sheet.

So that the summary sheet looks like this

Emp# NAME Hours Worked
1234 John 30
1235 Pete 24
1236 Sue 40

I hope I made what I would like to do clear enough. Any help would be
greatly appreciated. Is there any way I can do this WITHOUT a macro? I
don't understand programming like that, but I am fairly good with
formulas.

Thanks.