An entire day wasted trying to use VLOOKUP across multiple worksheets

[adam.hoyt]

Here's my problem:

1. I have a workbook with approximately 100 worksheets. Each worksheet contains details from an invoice which are broken out into various categories of costs (15-20 "buckets" of costs).
2. Have multiple invoices (ie. worksheets) for some service providers.
4. Need to make a summary sheet which shows the total amounts for each cost/bucket for each service provider.


I've tried using a combination of functions (SUMIF, VLOOKUP, INDIRECT, etc.) but can't figure out how to create a formula which looks through each worksheet (ie invoice) for a match on the service provider and then another function (such as VLOOKUP) which pulls each cost to the summary sheet.

Illustration:

Summary Sheet

Service Provider: Bucket A Bucket B Bucket C Etc.
#1 BofA
#2 Microsoft
#3 Etc.


Invoice

Service Provider:
#1
BofA

Bucket:
Amount
A $$$
B $$$
C $$$


I can't figure out how to get an IF statement to lookthrough the entire workbook and pull these costs when I get a match for the service provider. This sounds simple, why does it have to be so difficult!!

Any suggestions would be greatly appreciated,
Adam

[vane0326]

May I see a small sample of your workbook with the expected results?