Pivot Table
Posted by Alex on April 04, 2001 5:55 AM
Hello, i will describe my problem:
I need to split 1 pivot table in 2 separate tables based on the same data.
I need the second table updating upon changes made to the page field of the first table.
Is it possible with pivot tables or should I use another method?
Thank you and good bye
Alex Zambonin - Webmaster Librinet.it
webmaster@librinet.it
================================================
www.librinet.it
Librinet è un marchio di Adler Libri srl
tel. 049 8840010 fax 049 8840444
Via Breda 26 35010 Limena - PD
================================================
Posted by Mark W. on April 04, 2001 7:06 AM
Alex, you can make a copy of a PivotTable and both
tables will be linked to the same data, and
refreshed when either table is refreshed. Unfortunately,
that's the extent of the sharing between these
two tables. I suppose you could have the shared
data set "read" the Page field settings of the 1st
PivotTable and convert that to a boolean value that
the 2nd would use. For example, suppose the
Page field of the 1st was 'Fruit' and set to "Apples".
You could have a column in your data set labelled
'Selected Fruit' that compares each row of the data
to "Apples" and returns TRUE or FALSE. The 2nd
PivotTable would use the 'Selected Fruit' as a
Page field that is always set to TRUE.
Posted by Alex on April 05, 2001 1:18 AM
Do you mean I must create a calculated field that checks the selected value? To do this it should compare the selected value with every record of that column? How can I do this?
Thank you for you answer.
Alex
Posted by Mark W. on April 05, 2001 6:39 AM
Alex, suppose that you have a data list in cells
A1:B4 that contains:
{"Fruit","Quantity";
"Apples",10;
"Oranges",20;
"Lemons",30}
Construct a PivotTable with 'Quantity' in the
DATA area and 'Fruit' in the PAGE area, and
place this table at cell E1. Next, copy this
PivotTable to cells H1:I4. In cell C1 add the
label, 'Selected Fruit', and enter the formula,
=$F$1=$A2, into cell C2 and copy down. Edit the
2nd PivotTable replacing the 'Fruit' page field
with 'Selected Fruit'. Set 'Selected Fruit' to
TRUE. Now, after you can change the page selection
of the 1st PivotTable and Refresh the 2nd PivotTable
will show the same value as the 1st.
Posted by Mark W. on April 05, 2001 6:42 AM
Procedural change...
Actually, it would be best if you had the column
header, 'Selected Fruit', in cell C1 before you
create the 1st PivotTable.
Posted by Alex on April 10, 2001 7:54 AM
Ok that works perfectly, it will be very useful for me but my situation (i didn't explained it completely, sorry) is different:
1) My data is an access database (so I can't update it dinamically upon cell changes in excel)
2) I need automatic refresh of both tables, without pressing the refresh button.
Maybe I should leave excel and use access?
Thank you for your answers!
Alex
