>Using only built-in functions, it gets very ugly
It's not all that ugly and it works! Very nice!
I was headed in the right direction but was still a long ways off.
Wonder how the OP made out?
Biff
"Harlan Grove" <
[email protected]> wrote in message
news:
[email protected]...
> Biff wrote...
>>>That said, if all the values were positive *and* there were only one
>>>entry matching A1 in each other worksheets' A3:A30 range, then this
>>>should work (at least it works in my tests).
>>
>>Yes, it works under those conditions, where there is only a single
>>instance
>>(or none) per sheet. I was working on a solution in which there were
>>multiple instances per sheet. How could that be done? Nothing I've tried
>>works. The difficult part is the count.
> ...
>
> There are times it's a PITA that Excel really isn't a 3D spreadsheet.
>
> The simplest solution would be using Laurent Longre's MOREFUNC.XLL
> add-in, specifically, using that add-in's THREED function.
>
> =SUMPRODUCT(--(THREED(A:Z!$A$4:$A$30)=A4),THREED(A:Z!$D$4:$D$30))
> /SUMPRODUCT((THREED(A:Z!$A$4:$A$30)=A4)*(THREED(A:Z!$D$4:$D$30)>0))
>
> Using only built-in functions, it gets very ugly, but it's possible. If
> WSL, the named list of worksheets to process, were a horizontal array,
>
> =SUMPRODUCT(
> --(T(OFFSET(INDIRECT("'"&WSL&"'!A4:A30"),ROW(INDIRECT("4:30"))-4,0,1,1))=A4),
> N(OFFSET(INDIRECT("'"&WSL&"'!D4:D30"),ROW(INDIRECT("4:30"))-4,0,1,1))
> )/SUMPRODUCT(
> (T(OFFSET(INDIRECT("'"&WSL&"'!A4:A30"),ROW(INDIRECT("4:30"))-4,0,1,1))=A4)*
> (N(OFFSET(INDIRECT("'"&WSL&"'!D4:D30"),ROW(INDIRECT("4:30"))-4,0,1,1))>0)
> )
>
Bookmarks