Sumproduct Formula

[Jeff Podgorski]

Hello,

I'm using =SUMPRODUCT(--('[Payroll 01-03-11.xls]Chassis 1'!$F$12:$F$26="61153"),'[Payroll 01-03-11.xls]Chassis 1'!$P$12:$P$26) and it is calculating correctly, I need to run this formula against every payroll sheet for 2011, is there an elegant way to copy this formula so the date range changes automatically so I don't have to modify the formula manually for each day?

I could also run the formula next to a list of dates and have it reference them, but I have no idea how to modify the formula for that to work.

Hope this makes sense.

Thanks

 

[sheetspread]

You can use an indirect formula but if the sheets are in different workbooks they all have to be open (you'd also use YEAR and perhaps VALUE with the extracted date portion). Sounds like VBA is much better suited for this task.

 

[Jeff Podgorski]

Can you give me an example, I don't have a lot of experince with those functions.

Thanks

Yes, use the YEAR function. You may have to extract the date portion if it's in a text string and use VALUE too.

 

[sheetspread]

I tried the formula approach without VBA but got a REF error. It might not even be possible to combine sumproduct and indirect across workbooks. If someone here can't write some code to do this then you might consider a macro that converts your workbooks into tabs of a single workbook then another macro that stacks all the tabs in a single tab (these are easier to find).