SUM across multiple sheets when using VLOOKUP

[scuttleButt]

Hi,

I have 8 worksheets with people entering times on various projects. Each person has 4 - 10 projects on their worksheet. On the project summary I want to search the 8 worksheets and find out who is on each project (via project ID) and total their time. So if 2 of the 8 people are on a project, I want to sum their time together. I have my VLOOKUP working to where it finds and displays the first occurance but I want it to sum all the occurances. Can anyone help? Here is my formula:
=VLOOKUP(C30,INDIRECT("'"&INDEX(associate,MATCH(TRUE,COUNTIF(INDIRECT("'"&associate&"'!A:A"),C30)>0,0))&"'!A:J"),9,0)

Associate is a named range that point to a list of the 8 worksheet names. The value that I want to lookup (project ID) is in cell C30 and I want to search A:A (project ID) in the 8 worksheets. A:J is the table and column 9 has the times that I want to sum.

Can any one help? Much appreciated!

 

[AnotherHiggins]

Surround your vlookup in an IF statement so that if it doesn't find a match on a sheet, it returns zero instead of an error. Once you have that down, do a vlookup for the first sheet, add to that a vlookup for the second sheet, etc.

It will look something like this:

=If(IsError(VLookupForSheet1), 0, VLookupForSheet1) + If(IsError(VLookupForSheet2), 0, VLookupForSheet2) + ...

Big pain, right? That leads to the larger point -

you should never store your data broken out onto different sheets!!!!!

You should have all data on a single sheet with Name as a column. Once you have 'Normalized' your data, your life will become much easier. You can easily and quickly generate reports showing any specific person or any group of people. If you have dates as a column, you can easily pull out data for last week, last month, 3rd quarter of last year, etc.



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

To echo John's exhortation, when you chop similar data up into different locations, you utterly destroy it's usefulness and render Excel's plethora of data analysis, lookup and reporting features impotent.

I would STRONGLY urge you to consolidate your data, from which you will be able to do exceedingly more than you can, hobbling around with this pooly designed workbook.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[scuttleButt]

I did not design the workbook, I just inherited it. This was made years ago and I can't change it. I can only work with what I have.

Really nice forum. A person comes here looking for help and all I get is some folks sitting on their high-horse.

Never mind

 

[SkipVought]

"...all I get is some folks sitting on their high-horse."

Not really. Do you not want to hear that there are BETTER and acceptable practices.

Too many novice spreadsheet users wind up underusing and missusing this wonderful tool.

Okay, you have a valid limitation -- you do not have the authority to change the structure of what users enter data in. However, you could use a combining technique, using MS Query to JOIN the data in these 8 sheets into ONE for the purpose of analysis and reporting. faq68-5829.

I you wish to pursue, post back and explore a series of UNION ALL queries.



Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[xlbo]

scuttlebut - the difference between this and most other forums is that the emphasis here is on learning - not just providing solutions to existing problems but helping people understand that some of these problems need not exist. In your case, so that next time you do have control over the design of the workbook, you are aware of the best way to structure the data such that you don't have this kind of unnecessarily complex issue again.

If you are that averse to people helping you learn, there are plenty of other forums where you can get just the answers to the questions you ask and you can happily continue having to overcome issues that you don't have to.

Your choice

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

Calm down. There are no high horses here.

Want to know how I know it's so hard to deal with a spreadsheet like the one you have? Because I've made them.

We are speaking out of experience and, as Skip and Geoff pointed out, trying to help you learn.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[white605]

John - You cannot lay claim on making them, because I have made several myself!

Skip - Excellent suggestion, I have to join and union my data from "normalized tables" regularly to report on them with xcel

wjwjr

This old world keeps spinning round - It's a wonder tall trees ain't layin' down