Using GETPIVOTDATA with OFFSET? Could there be another solution.

[Freakazoid]

Hey all

I have run into another problem.

I have an Excel worksheet working as some sort of a dashboard, within this dashboard I have made cells that changes when I slide a scrollbar down, this is done by using the OFFSET-function, inserting the scrollbars linked cell as the row refference in OFFSET.

My problem is that the data I would like to show in my scrollable area comes from a pivottable, the offset function works great for getting text from the cells in the pivottable. But when i try the same thing with a cell containing numbers I do not succeed.

My first idea was to use the GETPIVOTDATA within the OFFSET-function like this:

=OFFSET((GETPIVOTDATA(data_field;pivot_table;[field1];[item1]));rows;0)

But it does'nt seem to work i get the error: The formula you typed contains an error.

Highlighting the getpivotdata part of my formula.


Can anybody help me with a solution to this problem? Or tell me if there is another way to get the data from a pivot table into a scrollable area like mine.

Thanks in advance

/Freakazoid

[Freakazoid]

I have now solved the problem myself, it turned out not to be that big a problem in the end.

The solution:

Instead of using the automated formula GETPIVOTDATA when a cell in the pivot table is selected Excel is able to retrieve the data in the regular way if you just write the formula yourself.

For example instead of the automated;

=OFFSET((GETPIVOTDATA(data_field;pivot_table;[field1];[item1]));rows;0)

I just wrote:

=OFFSET(data_sheet!data_field;rows;0)

And the problem is solved there is no need to use the GETPIVOTDATA function in my case.

/Freakazoid

[Oksana]

HI!
I am struggling to fully understand the above solution; but think I have the same issues you mentioned above. I have a sheet where I set what month of data the formulas need to look at. My source data (cannot change format) has each month's worth of data in a separate column for the required row titles. My pivot table makes visible the row titles I need and then has each month's worth of data in the columns. When I set the sheet to 'March' I need it to sum the first 3 columns of pivot data in the cell I'm looking to return the data into. I've already created a lookup named range that says Jan = 0, Feb = 1, Mar = 2, etc...thinking that I could use the offset function to tell it to move out 1 additional column for Feb, 2 additional columns for March, etc. However, I'm really struggling to build the formula. Any help would be much appreciated.

Thanks!

p.s. I need it to do this for a number of different rows that may change over time; this is why I was trying to use getpivotdata as opposed to hardcoding the cell address.

[Oksana]

Hi!
I solved my issue as follows:
As much as I couldn't change my source data I could add to it; so I added a column to the source data that used the offset function on every row to tell me the year to date total I was looking for on a line by line basis. Then, instead of trying to get my results cell to lookup pivot data dynamicaly, i just looked up the 'YTD total' column that is now added to my pivot table. Now I don't need to look up pivot data dynamically at all, I'm just getting the data i need into the pivot table in the first place.
Thanks.