sumif using other cell contents as workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
Click to expand...
 
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 said:
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
Click to expand...
Click to expand...
 
=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

Dave Peterson said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top