I've searched far and wide on the board and am coming up empty. Can someone please help me figure out why the following won't work?
=SUMPRODUCT(--('[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$A$5:$A$175>0),--(("'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!" & ADDRESS(5,112) & ":" & ADDRESS(175,112))>0))
I'm getting a #VALUE! error
When I do this part by itself:
"'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!" & ADDRESS(5,112) & ":" & ADDRESS(175,112)
I get what I want:
'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$DH$5:$DH$175
and when I do a straight:
=SUMPRODUCT(--('[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$A$5:$A$175>0),--(('[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$DH$5:$DH$175)>0))
I get 8, which is what I want....
So, how come it won't work in the original (albeit messy) formula?
Ultimately, I need the column to be variable based on an index/match. So i was trying to use the address formula so I could then point the column (112 in this example) to another cell where I've done the index/match piece....
Thoughts?
SFB
=SUMPRODUCT(--('[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$A$5:$A$175>0),--(("'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!" & ADDRESS(5,112) & ":" & ADDRESS(175,112))>0))
I'm getting a #VALUE! error
When I do this part by itself:
"'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!" & ADDRESS(5,112) & ":" & ADDRESS(175,112)
I get what I want:
'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$DH$5:$DH$175
and when I do a straight:
=SUMPRODUCT(--('[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$A$5:$A$175>0),--(('[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$DH$5:$DH$175)>0))
I get 8, which is what I want....
So, how come it won't work in the original (albeit messy) formula?
Ultimately, I need the column to be variable based on an index/match. So i was trying to use the address formula so I could then point the column (112 in this example) to another cell where I've done the index/match piece....
Thoughts?
SFB