Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying cell names | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Export TextBox contents to new workbook. | Excel Discussion (Misc queries) | |||
Write Contents of TextBox to new Workbook | Excel Discussion (Misc queries) | |||
Adding contents of one cell to a range of cells. | Excel Worksheet Functions |