V Lookup between worksheets in same file

A

Alberta Rose

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
 
J

Joel

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)))
 
A

Alberta Rose

What is the vertical symbol between Apr and A1?

Joel said:
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)))
 
J

Joel

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)))
 
A

Alberta Rose

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.
 
D

Don Guillett

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)))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top