sumif using other cell contents as workbook

[Nigel]

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]

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.

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

 

[Nigel]

Hi Dave,

so can it be used with VLOOKUP? the month in the cell will change every
month so then start looking in a new workbook. my biggest problem is getting
the month and year into the formula from a cell value.
maybe it could be compiled in a macro.....

thanks,

nigel

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.

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]

=vlookup() will work with closed workbooks--but it'll only return one value.

And you still can't use =indirect() within that =vlookup().

Try recording a macro when you either do an Edit|replace or Edit|links|change
source.

Hi Dave,

so can it be used with VLOOKUP? the month in the cell will change every
month so then start looking in a new workbook. my biggest problem is getting
the month and year into the formula from a cell value.
maybe it could be compiled in a macro.....

thanks,

nigel

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.

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