I think you have a couple of problems.

First, the function you'd want to use is =indirect(). But that won't work with
a closed workbook. You could change the links manually (edit|links) to point at
new month. You could also use a macro to change the links (if you knew what the
before and after should be (record a macro when you do it manually).

But the second problem is that =sumif() won't work with closed workbooks,
either.

You could replace it with something like:

=sumproduct(--('yourpath\["November 2005".xls]Front Order Sheet!B10:B1010=c2),
('yourpath\["November 2005.xls"]Front Order Sheet!F10:F1010))

But that still leaves the first problem.


Nigel wrote:

Hi,

Can anyone help? i need to create a sumif formula or vba macro to carry out
the following: ( where "this word" is in E15 on the sheet)

=sumif(S:\Purchasing\["November 2005".xls]Front Order
Sheet!B10:B1010,C2,S:\Purchasing\["November 2005.xls"]Front Order
Sheet!F10:F1010)

where "November 2005" is in the formula, i need this to be collected from
cell E15 as it is different for each month so it looks in the relative month
workbook.

can anyone help?

regs,

Nigel

--

Dave Peterson