sumproduct excel 2007

[MAANI]

Sheet 1
Date Badge # wo # Manhrs
12-Sep-09 233 458 4.2
12-Sep-09 452 458 5.8
12-Sep-09 367 328 1.0
13-Sep-09 459 466 5.4

Sheet 2
B1 to CR1 Dates
A2 to A300 Badge #s
I want to input wo # in A1 so that total manhours will be calculated,example:
If I input 458 in A1, I want to get 4.2 in the cross match of 12-Sep and
badge # 233
so its kind of sumproduct of date,badge# and wo #,this is too slow because
sheet 1 has 500 000 rows filled.I'm using excel 2007.Any suggestions?

 

[Don Guillett]

Excel doesn't like large databases (especially in an EXTERNAL file). Try
looking in lesser rows or try a FINDNEXT macro instead.

 

[Ashish Mathur]

Hi,

How about creating a pivot table. Drag Date to the row area, badge number
to the column area and and wo# to the page field area. Then drag manhours
to the data area.

I expect this to drastically increase your file size. Leaving the issue of
file size aside (which can be addressed by creating the pivot in a separate
file), what problem do you face with this approach.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com