I'm trying to sum quarterly hours depending on employee name. Employee name
resides in column A of each worksheet. I have name defined the areas on the
sheets where the data resides as APR MAY JUN.

=vlookup(A5, Apr:Jun - what's next????

I want excel to look at the name in cell A5 on each sheet and if there is a
match, go to cell B5 and sum, C5, D5, etc. etc.

How do I do this? Help please...

Thanks, Laurie

Vlloup won't work across multiple sheets but Sum will. use indirect to get
the address and feed it into a sum like this. Mtch will return the row
number where the person name was found provided the range starts in row 1.

=SUM(indirect("Apr:Jun!A"&match(A5,Apr|A1:A100)))


"Alberta Rose" wrote:

I'm trying to sum quarterly hours depending on employee name. Employee name
resides in column A of each worksheet. I have name defined the areas on the
sheets where the data resides as APR MAY JUN.

=vlookup(A5, Apr:Jun - what's next????

I want excel to look at the name in cell A5 on each sheet and if there is a
match, go to cell B5 and sum, C5, D5, etc. etc.

How do I do this? Help please...

Thanks, Laurie

What is the vertical symbol between Apr and A1?

"Joel" wrote:

Vlloup won't work across multiple sheets but Sum will. use indirect to get
the address and feed it into a sum like this. Mtch will return the row
number where the person name was found provided the range starts in row 1.

=SUM(indirect("Apr:Jun!A"&match(A5,Apr|A1:A100)))


"Alberta Rose" wrote:

I'm trying to sum quarterly hours depending on employee name. Employee name
resides in column A of each worksheet. I have name defined the areas on the
sheets where the data resides as APR MAY JUN.

=vlookup(A5, Apr:Jun - what's next????

I want excel to look at the name in cell A5 on each sheet and if there is a
match, go to cell B5 and sum, C5, D5, etc. etc.

How do I do this? Help please...

Thanks, Laurie

It was a typo. Should of been ! to indicate the sheet name and range. I
performed a lookup of the Apr sheet for the employeee name. It doesn't make
a difference which sheet to use for the match becasue all the sheets have the
same employee in the same row.

=SUM(indirect("Apr:Jun!A"&match(A5,Apr!A1:A100)))




"Alberta Rose" wrote:

What is the vertical symbol between Apr and A1?

"Joel" wrote:

Vlloup won't work across multiple sheets but Sum will. use indirect to get
the address and feed it into a sum like this. Mtch will return the row
number where the person name was found provided the range starts in row 1.

=SUM(indirect("Apr:Jun!A"&match(A5,Apr|A1:A100)))


"Alberta Rose" wrote:

I'm trying to sum quarterly hours depending on employee name. Employee name
resides in column A of each worksheet. I have name defined the areas on the
sheets where the data resides as APR MAY JUN.

=vlookup(A5, Apr:Jun - what's next????

I want excel to look at the name in cell A5 on each sheet and if there is a
match, go to cell B5 and sum, C5, D5, etc. etc.

How do I do this? Help please...

Thanks, Laurie

the same employee is not always on the same row, as some employees are added
and some deleted. When I type this formula in, I get a N/A# error. I've
made sure all numerical cells are formatted as number.



"Joel" wrote:

It was a typo. Should of been ! to indicate the sheet name and range. I
performed a lookup of the Apr sheet for the employeee name. It doesn't make
a difference which sheet to use for the match becasue all the sheets have the
same employee in the same row.

=SUM(indirect("Apr:Jun!A"&match(A5,Apr!A1:A100)))




"Alberta Rose" wrote:

What is the vertical symbol between Apr and A1?

"Joel" wrote:

Vlloup won't work across multiple sheets but Sum will. use indirect to get
the address and feed it into a sum like this. Mtch will return the row
number where the person name was found provided the range starts in row 1.

=SUM(indirect("Apr:Jun!A"&match(A5,Apr|A1:A100)))


"Alberta Rose" wrote:

I'm trying to sum quarterly hours depending on employee name. Employee name
resides in column A of each worksheet. I have name defined the areas on the
sheets where the data resides as APR MAY JUN.

=vlookup(A5, Apr:Jun - what's next????

I want excel to look at the name in cell A5 on each sheet and if there is a
match, go to cell B5 and sum, C5, D5, etc. etc.

How do I do this? Help please...

Thanks, Laurie

Maybe adapt this to your needs to find person in f11 and sum the entire row
=SUM(INDIRECT(MATCH(F11,G:G,0)&":"&MATCH(F11,G:G)) )

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Alberta Rose" wrote in message
...
I'm trying to sum quarterly hours depending on employee name. Employee
name
resides in column A of each worksheet. I have name defined the areas on
the
sheets where the data resides as APR MAY JUN.

=vlookup(A5, Apr:Jun - what's next????

I want excel to look at the name in cell A5 on each sheet and if there is
a
match, go to cell B5 and sum, C5, D5, etc. etc.

How do I do this? Help please...

Thanks, Laurie