Formula in 2 different workbooks question?

**pogo.stix** · 07-18-2011, 05:52 PM

Ok, so using the formula below, how would I use it within 2 workbooks.

Say for instance a workbook contains one speadsheet "A" Now I want to use the below formula in Workbook "B" Speadsheet "C".

=SUMPRODUCT(--(A2:A200>=DATE(2011,7,1)),--(A2:A200<=DATE(2011,7,15)),--(C2:C200="Wages"),D2:D200)

I do not wish to change the above fomula as it works in a single speadsheet fine all I want it to do is the calculations from within spreadsheet "C"

Thanks in advance if you can help me

**teylyn** · 07-18-2011, 06:33 PM

Hello,

you can reference an external workbook and a specific sheet therein by using the syntax

'[filename.xls]Sheetname'!A1

Applied to your formula: This formula in Workbook B, assuming that the sheet in Workbook A is called Sheet1

=SUMPRODUCT(--('[A.xlsx]Sheet1'!A2:A200>=DATE(2011,7,1)),--('[A.xlsx]Sheet1'!A2:A200<=DATE(2011,7,15)),--('[A.xlsx]Sheet1'!C2:C200="Wages"),'[A.xlsx]Sheet1'!D2:D200)

**pogo.stix** · 07-18-2011, 07:55 PM

Ah thanks very much for this help, works like a dream. Can i ask just one other question?

Say for instance i did not want to limit the cells to any number, for example Sheet1'!D2:D200) is there a way so I can make the D200 infinate?

**teylyn** · 07-18-2011, 11:42 PM

This is not advisable, at least not with Sumproduct. It can become very slow.

You can create dynamic range names for the data columns instead. These will grow and shrink with the data and you can use the range names instead of the cell references

=SUMPRODUCT(--('[A.xlsx]Sheet1'!Dates>=DATE(2011,7,1)),--('[A.xlsx]Sheet1'!Dates<=DATE(2011,7,15)),--('[A.xlsx]Sheet1'!Category="Wages"),'[A.xlsx]Sheet1'!Amounts)

This formula uses the range names Dates, Category, Amounts. Check out this article to learn about how to define dynamic range names.

http://www.contextures.com/xlnames01.html

cheers,