Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically generated content
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamically Generated Range using Macro | Excel Worksheet Functions | |||
Dynamically generated content | Excel Programming | |||
Dynamically generated content | Excel Programming | |||
How to determine which dynamically generated control was clicked | Excel Programming | |||
How to code event for dynamically generated checkboxes | Excel Programming |