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

To see if I understand correctly, let me paraphrase.

On the raw data sheet, there are no headers. There is a name in column BV, and
73 pieces of information about that person in the 73 columns to the left of
the name (A:BU). Each piece of information is either a number (1-5), NA, or
blank. For each person you have multiple sets of the 73 answers, and the
number of those sets varies.

re the pivot table: does it do anything more than give you a sorted list of
the unique names? The 2nd column is "totals". Totals of what? Do you mean the
count, i.e. how many rows there were for that person? If so, do you need that,
or did you just add it because a pivot table needs a data field?

One thought that comes to mind is to use SUBTOTALs to generate the counts and
average, rather than array formulas. SUBTOTAL excludes values from rows that
are hidden by a filter.

You need 3 subtotals for each column. They would go in, say, A511:BU513

In A511, =SUBTOTAL(3,A$1:A$500) (3 is the COUNTA function - numbers + NAs)
In A512, =SUBTOTAL(2,A$1:A$500) (2 is COUNT - numbers only)
In A513, =SUBTOTAL(1,A$1:A$500) (1 is AVERAGE - of the numeric entries)

and copy the formulas across to BU. (BTW, do you want to see the count?
AVERAGE automatically excludes blanks and text.)

These are the steps the macro would take:

1. Count the number of rows on the data sheet, and put the formulas (with the
correct range references) in the proper rows below the data.

2. Turn on AutoFilter on the data sheet.

3. Fetch (the next) name from the Pivot Table.

4. Modify the filter criterion to filter on column BV for this person.

5. Recalculate.

6. Copy Data!A511:BU513 to the next available column on the 3rd sheet
(presumably the name gets copied, too, but I don't know where).

7. If we have processed the last name, quit. If not, repeat steps 3-6 for the
next name.


On Tue, 12 Oct 2004 15:19:30 -0500, ShadowAce
wrote:


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 first
column is the number of responses (cells with data in them) that belong
to that person:

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

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

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

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

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

Notice that these are all array formulae.

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

In the third formula above, the D3 denominator is the result of the
second formula. These three formulae are copied through 73 rows,
incrementing Data!$B to Data!$C to Data!D, etc., while Data!$BV remains
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 three
formulae dynamically, based on the number of names in the Pivot table.

Thanks for your patience and help!

PS: can you email me a workbook with the raw data and the pivot table?

myrnalarson (at) charter (dot) net


On Tue, 12 Oct 2004 15:19:30 -0500, ShadowAce
wrote:


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 first
column is the number of responses (cells with data in them) that belong
to that person:

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

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

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

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

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

Notice that these are all array formulae.

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

In the third formula above, the D3 denominator is the result of the
second formula. These three formulae are copied through 73 rows,
incrementing Data!$B to Data!$C to Data!D, etc., while Data!$BV remains
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 three
formulae dynamically, based on the number of names in the Pivot table.

Thanks for your patience and help!

I've sent Michael a workbook with code that does what I describe below. If
anyone is interested in seeing it, I will post it.

On Tue, 12 Oct 2004 22:51:15 -0500, Myrna Larson
wrote:

To see if I understand correctly, let me paraphrase.

On the raw data sheet, there are no headers. There is a name in column BV,
and
73 pieces of information about that person in the 73 columns to the left of
the name (A:BU). Each piece of information is either a number (1-5), NA, or
blank. For each person you have multiple sets of the 73 answers, and the
number of those sets varies.

re the pivot table: does it do anything more than give you a sorted list of
the unique names? The 2nd column is "totals". Totals of what? Do you mean the
count, i.e. how many rows there were for that person? If so, do you need
that,
or did you just add it because a pivot table needs a data field?

One thought that comes to mind is to use SUBTOTALs to generate the counts and
average, rather than array formulas. SUBTOTAL excludes values from rows that
are hidden by a filter.

You need 3 subtotals for each column. They would go in, say, A511:BU513

In A511, =SUBTOTAL(3,A$1:A$500) (3 is the COUNTA function - numbers +
NAs)
In A512, =SUBTOTAL(2,A$1:A$500) (2 is COUNT - numbers only)
In A513, =SUBTOTAL(1,A$1:A$500) (1 is AVERAGE - of the numeric entries)

and copy the formulas across to BU. (BTW, do you want to see the count?
AVERAGE automatically excludes blanks and text.)

These are the steps the macro would take:

1. Count the number of rows on the data sheet, and put the formulas (with the
correct range references) in the proper rows below the data.

2. Turn on AutoFilter on the data sheet.

3. Fetch (the next) name from the Pivot Table.

4. Modify the filter criterion to filter on column BV for this person.

5. Recalculate.

6. Copy Data!A511:BU513 to the next available column on the 3rd sheet
(presumably the name gets copied, too, but I don't know where).

7. If we have processed the last name, quit. If not, repeat steps 3-6 for the
next name.


On Tue, 12 Oct 2004 15:19:30 -0500, ShadowAce
wrote:


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 first
column is the number of responses (cells with data in them) that belong
to that person:

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

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

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

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

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

Notice that these are all array formulae.

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

In the third formula above, the D3 denominator is the result of the
second formula. These three formulae are copied through 73 rows,
incrementing Data!$B to Data!$C to Data!D, etc., while Data!$BV remains
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 three
formulae dynamically, based on the number of names in the Pivot table.

Thanks for your patience and help!

PS: The job turned out to be more complicated than I thought. Since he created
a pivot table from his raw data, I assumed it had a header row. It didn't.
AutoFilter requires one. Without it, the first data row is treated as the
header, and that one row wasn't included in the subtotals for that person (the
range used in the subtotal formulas started at row 2).

On Wed, 13 Oct 2004 22:51:47 -0500, Myrna Larson
wrote:

I've sent Michael a workbook with code that does what I describe below. If
anyone is interested in seeing it, I will post it.

On Tue, 12 Oct 2004 22:51:15 -0500, Myrna Larson
wrote:

To see if I understand correctly, let me paraphrase.

On the raw data sheet, there are no headers. There is a name in column BV,
and
73 pieces of information about that person in the 73 columns to the left of
the name (A:BU). Each piece of information is either a number (1-5), NA, or
blank. For each person you have multiple sets of the 73 answers, and the
number of those sets varies.

re the pivot table: does it do anything more than give you a sorted list of
the unique names? The 2nd column is "totals". Totals of what? Do you mean
the
count, i.e. how many rows there were for that person? If so, do you need
that,
or did you just add it because a pivot table needs a data field?

One thought that comes to mind is to use SUBTOTALs to generate the counts
and
average, rather than array formulas. SUBTOTAL excludes values from rows that
are hidden by a filter.

You need 3 subtotals for each column. They would go in, say, A511:BU513

In A511, =SUBTOTAL(3,A$1:A$500) (3 is the COUNTA function - numbers +
NAs)
In A512, =SUBTOTAL(2,A$1:A$500) (2 is COUNT - numbers only)
In A513, =SUBTOTAL(1,A$1:A$500) (1 is AVERAGE - of the numeric entries)

and copy the formulas across to BU. (BTW, do you want to see the count?
AVERAGE automatically excludes blanks and text.)

These are the steps the macro would take:

1. Count the number of rows on the data sheet, and put the formulas (with
the
correct range references) in the proper rows below the data.

2. Turn on AutoFilter on the data sheet.

3. Fetch (the next) name from the Pivot Table.

4. Modify the filter criterion to filter on column BV for this person.

5. Recalculate.

6. Copy Data!A511:BU513 to the next available column on the 3rd sheet
(presumably the name gets copied, too, but I don't know where).

7. If we have processed the last name, quit. If not, repeat steps 3-6 for
the
next name.


On Tue, 12 Oct 2004 15:19:30 -0500, ShadowAce
wrote:


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 first
column is the number of responses (cells with data in them) that belong
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 the
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 column
that belong to that person:

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

Notice that these are all array formulae.

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

In the third formula above, the D3 denominator is the result of the
second formula. These three formulae are copied through 73 rows,
incrementing Data!$B to Data!$C to Data!D, etc., while Data!$BV remains
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 three
formulae dynamically, based on the number of names in the Pivot table.

Thanks for your patience and help!