Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT With External Range Name?
I am able to pull through data with the formula posted below, however the
ranges need to be dynamic since the range is expanded daily. The ranges are setup with range names. However, when I attempt to change the hard coded cell references to range names, the formula retuens a #VALUE error. =SUMPRODUCT(--(YEAR([LOB_ALT.xls]RAW_DATA!$A$2:$A$26496)=2007),--(MONTH([LOB_ALT.xls]RAW_DATA!$A$2:$A$26496)=10),[LOB_ALT.xls]RAW_DATA!$D$2:$D$26496) The Range Names A $A$2:$A$26496 = DATERANGE $D$2:$D$26496 = VOLUME_ACT HELP!!! Thanks, Ray |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT With External Range Name?
You have text in your data, month and year return value errors if there is a
single text value in the range you are checking -- Regards, Peo Sjoblom "RayportingMonkey" wrote in message ... I am able to pull through data with the formula posted below, however the ranges need to be dynamic since the range is expanded daily. The ranges are setup with range names. However, when I attempt to change the hard coded cell references to range names, the formula retuens a #VALUE error. =SUMPRODUCT(--(YEAR([LOB_ALT.xls]RAW_DATA!$A$2:$A$26496)=2007),--(MONTH([LOB_ALT.xls]RAW_DATA!$A$2:$A$26496)=10),[LOB_ALT.xls]RAW_DATA!$D$2:$D$26496) The Range Names A $A$2:$A$26496 = DATERANGE $D$2:$D$26496 = VOLUME_ACT HELP!!! Thanks, Ray |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT With External Range Name?
Chocolate in my Peanutbutter maybe... But not text in my data.
Actually, to be honest, the last cell in the range is a text cell. However, to test I changed the named range to exclude that cell and I get the same result. Again, with hard coded cell references, i.e. [LOB_ALT.xls]RAW_DATA!$A$2:$A$26496 I get the desired result. When I change the absolute reference to a range name the formula bombs... Working under a deadline - any additional help, ideas, alternate solutions are greatly apprecaited! Thanks, Ray "Peo Sjoblom" wrote: You have text in your data, month and year return value errors if there is a single text value in the range you are checking -- Regards, Peo Sjoblom "RayportingMonkey" wrote in message ... I am able to pull through data with the formula posted below, however the ranges need to be dynamic since the range is expanded daily. The ranges are setup with range names. However, when I attempt to change the hard coded cell references to range names, the formula retuens a #VALUE error. =SUMPRODUCT(--(YEAR([LOB_ALT.xls]RAW_DATA!$A$2:$A$26496)=2007),--(MONTH([LOB_ALT.xls]RAW_DATA!$A$2:$A$26496)=10),[LOB_ALT.xls]RAW_DATA!$D$2:$D$26496) The Range Names A $A$2:$A$26496 = DATERANGE $D$2:$D$26496 = VOLUME_ACT HELP!!! Thanks, Ray |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT With External Range Name?
Sorry didn't catch that you were using names, how do you define your name
and where do you define your name. If you are using a formula in one workbook and referring to another workbook you can do insertnamedefine in the workbook that holds the formula =[LOB_ALT.xls]RAW_DATA!$A$2:$A$26496 for DATERANGE and do the same for VOLUME_ACT and include the workbook as well then this formula works for me =SUMPRODUCT(--(YEAR(DATERANGE)=2007),--(MONTH(DATERANGE)=10),VOLUME_ACT ) or use the named range name in the source workbook and use this formula =SUMPRODUCT(--(YEAR(LOB_ALT.xls!DATERANGE)=2007),--(MONTH(LOB_ALT.xls!DATERANGE)=10),LOB_ALT.xls!VOLU ME_ACT ) that also worked for me as you can see you don't need the brackets around the workbook name -- Regards, Peo Sjoblom "RayportingMonkey" wrote in message ... Chocolate in my Peanutbutter maybe... But not text in my data. Actually, to be honest, the last cell in the range is a text cell. However, to test I changed the named range to exclude that cell and I get the same result. Again, with hard coded cell references, i.e. [LOB_ALT.xls]RAW_DATA!$A$2:$A$26496 I get the desired result. When I change the absolute reference to a range name the formula bombs... Working under a deadline - any additional help, ideas, alternate solutions are greatly apprecaited! Thanks, Ray "Peo Sjoblom" wrote: You have text in your data, month and year return value errors if there is a single text value in the range you are checking -- Regards, Peo Sjoblom "RayportingMonkey" wrote in message ... I am able to pull through data with the formula posted below, however the ranges need to be dynamic since the range is expanded daily. The ranges are setup with range names. However, when I attempt to change the hard coded cell references to range names, the formula retuens a #VALUE error. =SUMPRODUCT(--(YEAR([LOB_ALT.xls]RAW_DATA!$A$2:$A$26496)=2007),--(MONTH([LOB_ALT.xls]RAW_DATA!$A$2:$A$26496)=10),[LOB_ALT.xls]RAW_DATA!$D$2:$D$26496) The Range Names A $A$2:$A$26496 = DATERANGE $D$2:$D$26496 = VOLUME_ACT HELP!!! Thanks, Ray |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
external data range a two way street? | Excel Discussion (Misc queries) | |||
Set external cell filter for a range | Excel Discussion (Misc queries) | |||
external named range problem | Excel Discussion (Misc queries) | |||
sumproduct external reference#2 | Excel Worksheet Functions | |||
sumproduct external reference | Excel Worksheet Functions |