"quailhunter"
wrote...
....
Here's my setup:
Sheet1 --- (This is where I'd like to store the average of sheet2 &
sheet3 for each person on Sheet1...)
Bob
Jim
Joe
Joex
Liz
Sue
Tom

Sheet2...
Bob 93
Jim 26
Joe 34
Joe 100
Sue 34
Tom 82

Sheet3...
Bob 66
Joe 0
Joex 93
Liz 88
Sue 9
Tom 7

So in Sheet1, in B4, I inserted Biff's function and get #NAME
returned....
....

What's the *EXACT* formula you're trying?

If you're using something like

=SUMPRODUCT(
--(T(OFFSET(INDIRECT("'"&WSL&"'!A4:A30"),ROW(INDIREC T("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(INDIREC T("4:30"))-4,0,1,1))=A4)*
(N(OFFSET(INDIRECT("'"&WSL&"'!D4:D30"),ROW(INDIREC T("4:30"))-4,0,1,1))0)
)

did you create a list of worksheet names and define the name WSL referring
to that list? If not, that'd explain the #NAME? error. If I define WSL
referring to

={"Sheet2","Sheet3"}

and put your sample names in col A and sample numbers in col D beginning in
row 4, then with Bob in cell A4 in Sheet1 and the other names below it, I
enter the *array* formula above in cell B4 in Sheet1, then fill it down into
B5:B10, I get the following results in A4:B10.

Bob 79.5
Jim 26
Joe 67
Joex 93
Liz 88
Sue 21.5
Tom 44.5