Ahh. OK. Everything is in the same workbook.

The raw data is in the first worksheet (I called it Data)
The Pivot table is in the 2nd worksheet. The names are in one column
going down, with the totals in a 2nd column.
The specific data are numbers from 1 to 5 or an "NA" or a blank cell.
I reference them by referencing the worksheet, then the cell ie
Data!$B3:$B500

On the third worksheet, I set up three columns of 73 rows. The firs
column is the number of responses (cells with data in them) that belon
to that person:

=SUM(IF(Data!$B$1:$B$500<"",IF(Data!$BV$1:$BV$500 =Pivot!$A$5,1,0)))

The second column is the number of responses useful for computing th
average of those (cells with data and not an "NA":

=SUM(IF(Data!$B$1:$B$500<"NA",IF(Data!$B$1:$B$500 <"",IF(Data!$BV$1:$BV$500=Pivot!$A$5,1,0))))

The third column is the average of cells in the Data worksheet colum
that belong to that person:

=SUMIF(Data!$BV$1:$BV500,Pivot!$A$5,Data!$B$1:$B50 0)/D3

Notice that these are all array formulae.

In the above formulae, Data!$B is the first of many columns to b
processed
Data!$BV contains the name, and Pivot!$A$5 is the first of the names i
the Pivot table, pulled from Data!$BV.

In the third formula above, the D3 denominator is the result of th
second formula. These three formulae are copied through 73 rows
incrementing Data!$B to Data!$C to Data!D, etc., while Data!$BV remain
constant as that is what is checking the name value in the rows.

So, what I am trying to do is create multiple tables of the above thre
formulae dynamically, based on the number of names in the Pivot table.

Thanks for your patience and help

--
ShadowAc
-----------------------------------------------------------------------
ShadowAce's Profile: http://www.excelforum.com/member.php...fo&userid=1522
View this thread: http://www.excelforum.com/showthread.php?threadid=26855