sumif using other cell contents as workbook
 

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

sumif using other cell contents as workbook
 

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

sumif using other cell contents as workbook
 

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


"Dave Peterson" wrote:

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

sumif using other cell contents as workbook
 

=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.



Nigel wrote:

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

"Dave Peterson" wrote:

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


--

Dave Peterson