Keep pivot references, really need help

B

Bokazoit

I currently work in two workbooks. One book containing the data and
workbook in which I would like to make references from the datasheet.

I have a huge amount of data so use pivot tables in my datasheet t
retrieve info. Part of the info I need is percentage of revenue fo
sub-categories for YTD as well as latest 3 month.

In my pivot in my datasheet I choose a sub-category and I choose
month (i.e. may). Until now I have entered the data for eac
sub-category manually for each month and YTD. So I wondered if it wa
possible to make cell-references from the pivottable in my datasheet t
my worksheet?

I tried to use the GETPIVOTDATA-function but as soon as I change th
month (i.e. from may to june) in my datasheet I loose the reference i
my worksheet :confused:

So is there anyway that I can make references from a pivot in
datasheet to a worksheet, and keep the correct data for each month?

If you don't know what I mean or think you need additional info plz as
instead of doing nothing :
 
B

Bokazoit

I use this formula:

=GETPIVOTDATA("Kampagner SOV";'[Gallup Rådata.xls]Pivo
CMO'!$A$11;"Brand";"PALMOLIVE")

I have attached a screendump of my pivot, so that you can see how th
looks and compare it to the reference. Tx :)
-Originally posted by VaughanùV®‚§v+º¢{&‰Ê貇í
-
Can you cut and paste your GETPIVOTDATA formula. My guess is tha
it specifies the month field in the formula. If so, when you chang
the month the formula will probably return an error (#N/A I think)
If you post your formula I can confirm that
Click to expand...


Attachment filename: excel example.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=61512
 
B

Bokazoit

bump :confused:

I still hope for help...

In my last post I attached the datasheet. I have added the workshee
here.

To be specific. For the datasheet:

At the top you see the 'Drop page field' were I can choose the year (Å
in the sheet), month (måned), category (kategori) etc.

I use the %-data under the 'total' and enter them into the worksheet
Under YTD03, YTD04, April, may, june. for each category.

As mentioned at in my first post, I would like to make a reference fro
the pivottable in the datasheet unto my worksheet, that are kept eve
though I change the 'month' ('måned' in the datasheet) in m
datasheet.

Anybody who has a solution :confused

Attachment filename: worksheet.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=61586
 
B

Bokazoit

I use the Datasheet and the worksheet. I enter data from the datashee
into the worksheet. So the worksheet is only references from th
datasheet.

Today I enter the data into the worksheet manually. I choose the mont
in my pivottable and it generates the SOV (Share Of Voice) for th
different categories. If you look at the picture of my datasheet, th
number I look for is those with % (i.e. 2,38%). For eac
category/product I enter these %-values into my worksheet.

So as an example I choose the month April in the pivot table in m
Datasheet and make a reference using the 'GETPIVOTDATA'-formula int
cell I26 (20%) for the first category, 22% for the second Category etc


Now the problem arises. Now I need the same data for May, so I choos
may in the pivottable in my datasheet, and the reference for Apri
changes to the may-data :confused: So what I get is an april that i
equal to the may-data.

There is nothing weird in that since the reference points at cells wer
the only difference is a change of month.

But what I need is a way that I can be able to keep the data intact fo
april when I switch to may in the pivot-table.

Do you get my question?...Seems that I don't make myself too clear :

Attachment filename: worksheet example.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=61603
 
V

Vaughan

That is enough now.

The GETPIVOTDATA() formula that you used does not specify the month, so it returns the data for the month displayed in the pivot table. Thus when the table shows April results, the formula returns April results, and when the table shows May, the formula also returns May.

The good news is, you can modify your GETPIVOTDATA formula to return the data for a particular month:

=GETPIVOTDATA("Kampagner SOV";'[Gallup RÃ¥data.xls]Pivot CMO'!$A$11;"Brand";"PALMOLIVE";"Month";"April")

Of course, instead of "April", you reference a cell with the relevant month in it.

The bad news is that the formula will not return any data that is not displayed by the table. So if you retain your current pivot table structure, where you display only one month at a time, your GETPIVOTDATA() formula will produce an error message unless the specified month is displayed.

The answer is to change the month field to a Column header field instead of a Page field. Then all the months will be displayed, and the correct data will be available for return by the GETPIVOTDATA() formula.

Hope this helps.

Vaughan
 
B

Bokazoit

Tx, but I'm afraid it's not enough. I can't change the month field to
coloum header. All the data fields are needed as is. You see, the SO
(Share Of Voice) is calculated as a parcentage of each brands amount o
commercials in a month. So if I change the month field to a colou
header I will get a SOV calculated as percentage of all months. So
SOV that is 18% in april, would suddenly become 2-3% because it'
calculated over all month YTD (January to June).

Ofcourse if there is a way to seperate the months then it's solved :p

I guess I'm lost soon. But I'm really puzzled that Microsoft mad
Pivottables if you can't retrieve data or make references to othe
sheets.

What I need is to copy a cell in the pivot into another sheet, and i
will not change it's value unless I update it :p

Any ideas what I can do Vaughan or others ofcourse :
 
Top